CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Daniel Robinson
Daniel Robinson

Reputation: 14848

SQL Server stored procedure parameter assignment

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

Upvotes: 0

Views: 1160

Answers (1)

Codesleuth
Codesleuth

Reputation: 10541

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.

Upvotes: 1

Related Questions