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

Question

I am trying to write a stored procedure to assist with development of our database, but I am having some trouble using it. For example:

DECLARE @pID int;
SET @pID = 1;
EXEC WriteLog 'Component', 'Source', 'Could not find given id: ' + CAST(@pID AS varchar);

This yields the error (on SQL Server 2005):

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '+'.

Can someone explain to me why my syntax is incorrect, and the right way to solve this problem?

Answer 1

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