Have Geek Brain

Sql job and datetime parameter

Another developer has created a stored procedure that is configured to run as a sql job every month. It takes one parameter, datetime. When I try to call it in a job or just in the query window, I get an error Incorrect syntax near ')'

. The call to execute it:

exec CreateHeardOfUsRecord getdate()

When I give it a hard coded date for example exec CreateHeardOfUsRecord '4/1/2010'

, it works fine. Any idea why I can't use it getdate()

in this context? Thanks.

+2


a source to share


4 answers


Parameters passed with Exec must be constants or variables . GetDate () is classified as a function. You need to declare a variable to store the result of GetDate () and then pass it to the stored procedure.



The supplied value must be constant or variable; you cannot supply a function name as a parameter value. Variables can be user-defined or system variables such as @@ spid.

+3


a source


by looking at EXECUTE (Transact-SQL)

[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }

you can only pass constant value or variable or DEFAULT clause

try:



create procedure xy_t
@p datetime
as
select @p
go

exec xy_t GETDATE()

exit:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
+1


a source


Try passing Convert (varchar, GetDate (), 101)

0


a source


Using Km code here is the way to do it

create procedure xy_t 
@p datetime 
as 
select @p 
go 

declare @date datetime

set @date = getdate() 

exec xy_t @date
0


a source






More articles:


All Articles