Using function as a parameter when executing a stored procedure? [duplicate]

Question

I'm testing a stored procedure and wanted to submit 'GETDATE()' function in place of parameter:

DECLARE @return_value int  EXEC @return_value = my_stored_procedure         @MyId = 1,         @MyDateField = GETDATE()  SELECT  'Return Value' = @return_value GO 

SQL Server 2005 complains with following error:

Incorrect syntax near ')'.

Anybody care to shed some light on the matter?

Answer 1

You can't use a function directly as a stored procedure parameter.

You can do the following:

DECLARE @now DateTime SET @now = GETDATE()  DECLARE @return_value int EXEC @return_value = my_stored_procedure         @MyId = 1,         @MyDateField = @now SELECT  'Return Value' = @return_value GO 

Answer 2

per MSDN

Execute a stored procedure or function [ { EXEC | EXECUTE } ]     {        [ @return_status = ]       { module_name [ ;number ] | @module_name_var }          [ [ @parameter = ] { value                             | @variable [ OUTPUT ]                             | [ DEFAULT ]                             }         ]       [ ,...n ]       [ WITH RECOMPILE ]     } [;]      Execute a character string     { EXEC | EXECUTE }          ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )         [ AS { LOGIN | USER } = ' name ' ]     [;]      Execute a pass-through command against a linked server     { EXEC | EXECUTE }         ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]             [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]         )          [ AS { LOGIN | USER } = ' name ' ]         [ AT linked_server_name ]     [;] 

Notice for @parameter you can either specify a value or a variable or specify Default. So you got to set the value of a variable as GetDate() (as others have specified) and use that variable.

HTH