Using function as a parameter when executing a stored procedure?

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?

score 31 · Answer 1 · edited Feb 05 '14 at 19:56

31

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

edited Feb 05 '14 at 19:56

Ascendant

2,430
3
26
34

answered May 11 '11 at 15:50

Oded

489,969
99
883
1,009

I know I can create variable, but I was curious about this construct. – krul May 11 '11 at 15:54
@krul - the phrase you used "shed some light on the matter" is ambiguous. If you wanted to ask why the restriction exists, you should ask exactly that. – Oded May 11 '11 at 16:07
3

@Oded: I'm not sure if krul cares, but I'd like to know. Why does this restriction exist? In just about every other language I know, you can use a function call as an argument, and it works in the expected way (function is evaluated, return value is passed as the argument). Why doesn't SQL allow this? – Tim Goodman Jun 25 '12 at 18:34
@TimGoodman - I don't have a reason for you, this is something only Microsoft can answer. At a guess, I believe the optimizer doesn't have enough statistics about the performance of different functions (don't forget that you can create user defined functions) so can't make good decisions. It is possible that a design decision was to simply disallow them (this is all conjecture, by the way). – Oded Jun 25 '12 at 18:53

score 17 · Accepted Answer · answered May 11 '11 at 15:58

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

thanks Raja, that was helpful, I have to learn reading posted definitions. — krul, May 11 '11 at 16:33

Martin Smith · Answer 3 · 2011-05-11T15:57:19.487

Function calls as parameters are not allowed (except for those system functions prefixed @@ - i.e. those that used to be called global variables)

You need to assign to a variable.

Microsoft acknowledge this is less than great in this related Connect item: T-SQL: use scalar functions as stored procedure parameters

Agreed! More generally, wherever TSQL expects, say, and integer value, it should accept a literal, a variable, or the result of a function whose return type is integer. It just makes the language more regular ("orthogonal") and easier to learn/use.

That said, it's too late for this feature in the Katmai release, but I'll add it to our TODO list.

score 2 · Answer 4 · answered May 11 '11 at 15:53

2

You could use

DECLARE @test DATE;
SET @test = GETDATE();

and then

DECLARE @return_value int
EXEC @return_value = my_store procedure
        @MyId = 1,
        @MyDateField = @test
SELECT  'Return Value' = @return_value
GO

answered May 11 '11 at 15:53

user748261

151
1
2
6

score -2 · Answer 5 · answered May 11 '11 at 15:50

-2

Try:

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

answered May 11 '11 at 15:50

garnertb

9,454
36
38

3

Doesn't work (with the ever-helpful message: Incorrect syntax) – Eliot Gillum Jan 27 '20 at 00:16

Using function as a parameter when executing a stored procedure?

5 Answers5

Linked