CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresparameter-passingsql-server-2014
freefaller
freefaller

Reputation: 19963

Passing function return value to stored procedure parameter

Is there a way to pass the return value of a function directly into a stored procedure parameter without first storing in a variable?

For example, given the following stored procedure (which will be on databaseA)

CREATE PROCEDURE [dbo].[MySproc]
   @NAME NVARCHAR(50)
AS

I would like to be able to call the following (from databaseB)...

EXEC [databaseA]..MySproc DB_NAME()

This results in the following error:

Incorrect syntax near ')'.

The following works, but requires two lines...

DECLARE @NAME NVARCHAR(50) = DB_NAME()
EXEC [databaseA]..MySproc @NAME

I realise on the surface this is not a big issue, but it would really help my situation if I could have it as a single line. My assumption is this is simply not possible

Upvotes: 0

Views: 769

Answers (1)

Thom A
Thom A

Reputation: 95827

No. From EXECUTE (Transact-SQL) the syntax for a stored procedure is the following:

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

Notice that for the parameters the passed value must be a (literal) value, a variable, or the DEFAULT keyword. It cannot be an expression, which would include something like the simple expression @Variable + 'string' and scalar functions like GETDATE().

As such you must store the value first in a variable and then pass that variable to the procedure.

Upvotes: 2

Related Questions