tags:

views:

124

answers:

4

Another developer created a stored procedure that is set up to run as a sql job each month. It takes one parameter of datetime. When I try to invoke it in the job or in just a query window I get an error "Incorrect syntax near ')'" The call to execute it is...

exec CreateHeardOfUsRecord getdate()

When I give it a hard coded date like

exec CreateHeardOfUsRecord '4/1/2010' it works fine. Any idea why I can't use getdate() in this context? Thanks.

+3  A: 

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

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

CodeByMoonlight
+1  A: 

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 in a constant value or a variable or the DEFAULT clause

try it out:

create procedure xy_t
@p datetime
as
select @p
go

exec xy_t GETDATE()

output:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
KM
OP mentions datetime vs.smalldatetime, however the data type doesn't really matter, it will fail before it gets that far. Try `exec xy_t '10'+'2'` using my sample procedure and you get `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '+'.`
KM
A: 

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

Broken Link
you can not pass an expression into a stored procedure, see my example code. trying your answer results in: `Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Convert'.`
KM
+1  A: 

Using Km's code here is a way to do it

create procedure xy_t 
@p datetime 
as 
select @p 
go 

declare @date datetime

set @date = getdate() 

exec xy_t @date 
Mike