Using the Result of an Expression (E.G. Function Call) in a Stored Procedure Parameter List

Using the result of an expression (e.g. Function call) in a stored procedure parameter list?

You need to use an intermediate variable. SQL Server does not support this kind of operation in the parameter list itself though it has been on the TODO list for a few years! (See Connect Item: Use scalar functions as stored procedure parameters)

The grammar for EXEC is

[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH  [ ,...n ] ]
    }
[;]

The documentation is not currently that clear on an acceptable format for value but it seems to be only "simple" expressions such as literal values or @@ prefixed system functions (such as @@IDENTITY). Other system functions such as SCOPE_IDENTITY() are not permitted (even those which do not require parentheses such as CURRENT_TIMESTAMP are not allowed).

So for the time being you need to use syntax such as the below

DECLARE @pID INT;

SET @pID = 1;

/*If 2008+ for previous versions this needs to be two separate statements*/
DECLARE @string VARCHAR(50) = 'Could not find given id: ' + CAST(@pID AS VARCHAR(11))

EXEC WriteLog
  'Component',
  'Source',
  @string 

Using function as a parameter when executing a stored procedure?

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

Executing stored procedure in T-SQL with dates as parameters

the braces of the function

  DATEFROMPARTS()

is causing the error.
So either pass date directly without the function or use a PLSQL block to do so by storing the result of the DATEFROMPARTS() in variable and then pass the variable to the stored procedure.

 DECLARE    
    @return_value int,
    @vDate1 date,
    @vDate2 date

SET @vDate1=DATEFROMPARTS(1900, 01, 01);
SET @vDate2=DATEFROMPARTS(2000, 01, 01);

EXEC @return_value = dbo.usp_GetTotalIncomeByPeriod 
                                @companyName = 'Exotic Liquids', 
                                @startDate = @vDate1,
                                @endDate = @vDate2;

Passing function return value to stored procedure parameter

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  [ ,...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.

Why do I get an error using CONVERT on one of the arguments of a stored proc call?

Stored procedure parameters can't contain expressions, they have to be constant values or @ variables. In your example, CONVERT() is an expression and is therefore not allowed.

The same is true for default parameter values.

Named stored proc parameters and concatenation

One way

declare @Var1 varchar(50)
select @Var1 = 'My name is: '+@Name

EXEC MyStoredProc @Param1=@Var1

The same is true with functions as well

you can't do this

EXEC MyStoredProc @Param1=getdate()

you need to do

declare @Var1 datetime
select @Var1 = getdate()

EXEC MyStoredProc @Param1=@Var1

---

Related Topics