locked
Stored Procedure Parameter Default Values RRS feed

  • Question

  • User2023521679 posted

    I'm trying to write a SQL-Server Stored Procedure with an Input Parameter "@DateUpdated" as DateTime and I want to give it a default value of GetDate(). Is this allowed? The Alter Procedure Statements below executed successfully, but when I go to run the Stored Procedure, I get the error message "Conversion failed when converting datetime from character string." I get the feeling that it thinks I am assigning the String "GetDate" rather than the function GetDate. The alter procedure statements would not work when I tried @DateUpdated       DateTime = GetDate(),

    Alter PROCEDURE AccidentAgentUpdate
       @AccidentAgentCode SmallInt,
       @AccidentAgentDesc VarChar(100),
       @SortOrder         SmallInt = 0,
       @DateUpdated       DateTime = GetDate,
       @UpdatedBy         VarChar(50) = HostName
    AS
    BEGIN
       SET NOCOUNT ON;
       Print @DateUpdated
       Print @UpdatedBy
      
      
    --   Update AccidentAgent
    --   Set AccidentAgentDesc   = @AccidentAgentDesc,
    --       SortOrder           = @SortOrder,
    --       Date_Updated        = @DateUpdated,
    --       Updated_By          = @UpdatedBy
    --   Where AccidentAgentCode = @AccidentAgentCode;
    END 

     

    Thursday, November 20, 2008 11:36 AM

Answers

  • User2023521679 posted

    Thank you. Apparently, you cannot use Functions as your default parameter values. My solution, as from the link is

     

    Set @DateUpdated = Coalesce(@DateUpdated , GetDate())

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 20, 2008 11:55 AM

All replies