Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Initialising parameter/variable

    Guys
    I'm having difficulty setting the default value getdate() to a date parameter

    CREATE PROC dbo.SPROCNAME
    @Name VARCHAR(60) ,
    @BusinessDay DATETIME = GETDATE() ,
    @Output VARCHAR(15) = 'Report'
    AS
    BEGIN.....

    When I try and run the SQL to create the proc I get the error message

    Server: Msg 170, Level 15, State 1, Procedure HDD_sp_Ins_HA_Period_Hypo, Line 4
    Line 4: Incorrect syntax near '('.

    Any ideas what is wrong with the syntax ?

    Thx in advance

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Default value must be constant or null value.

    u can rewrite ur sp to,
    Code:
    CREATE PROC dbo.SPROCNAME
    @Name VARCHAR(60) ,
    @BusinessDay DATETIME = '1900-01-01',
    @Output VARCHAR(15) = 'Report' 
    AS
    BEGIN
     
    if (@BusinessDay ='1900-01-01')
    set @BusinessDay =getdate()
    end
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    Thanks Mallier

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by SQL BOL
    @parameter

    Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined or the value is set to equal another parameter). A stored procedure can have a maximum of 2,100 parameters.

    Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.
    Emphasis mine

    I think you are going to have to supply this parameter. An alternative might be to declare the parameter as NULL and then test it in the first line of your stored proc. If the value is still null (ie, no value was supplied), then set it to the current date/time. I just tested that and it worked fine.

    Regards,

    hmscott


    Edit to add: I'm...so...sloooooow...
    Have you hugged your backup today?

  5. #5
    Join Date
    Oct 2005
    Posts
    37
    Thx also hmscott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •