SQL Server stored procedure parameter assignment

Question

Why can I not use functions to directly assign stored procedure parameters?

e.g.

exec myStoredProc @firstParam = aFunctionThatReturnsAnAppropriateValue()

but I find I have to decalre a variable just to hold the value

declare @temp type = aFunctionThatReturnsAnAppropriateValue()

exec myStoredProc @firstParam = @temp

which seems redundant

why can't I do it? I find the syntax in the first code snippet is not accepted by sql server management studio — Daniel Robinson
– Daniel Robinson, Commented Jul 6, 2012 at 8:57
It's invalid syntax. You can't pass a function as argument in an exec call. Only Microsoft can change that. — Andomar
– Andomar, Commented Jul 6, 2012 at 8:59
so why did you say it was redundant in your first comment? if you can't assign paramter values from functions directly then surely the temp variable is the way to do it? — Daniel Robinson
– Daniel Robinson, Commented Jul 6, 2012 at 9:03

Codesleuth · Accepted Answer · 2012-07-06 09:51:24Z

Quoting EXECUTE (Transact-SQL):

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH RECOMPILE ]
    }
[;]

You can see that it explicitly says @variable or value. I guess this is a language limitation, as you can neither write a call to a function as a variable or as a value; it is executable code (an expression), and the short-hand assignment during variable declaration is just a misleading bonus.

EDIT: Compare the difference of description for DECLARE and EXECUTE:

For DECLARE

=value
Assigns a value to the variable in-line. The value can be a constant or an expression, but it must either match the variable declaration type or be implicitly convertible to that type.

When looking through the page for EXECUTE, I do not see the mention of an expression. It seems to me that it would be handy, as I think you are trying to point out.

Collectives™ on Stack Overflow

SQL Server stored procedure parameter assignment

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related