How Can I Pass Expression Results as Parameters to SQL Server Stored Procedures?

Use expression input as stored procedure parameter

When creating a stored procedure, you may encounter difficulties using the result of an expression as a parameter. Here's an example:

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

However, this code throws an error (SQL Server 2005):

<code>Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.</code>

Grammar error explanation

SQL Server does not allow such operations in parameter lists. It expects arguments to be literal values, declared variables, or expressions without operators.

Solution: Use intermediate variables

To solve this problem, you must use an intermediate variable. The corrected code below demonstrates this approach:

DECLARE @pID INT;
SET @pID = 1;
DECLARE @logMessage VARCHAR(50) = 'Could not find given id: ' + CAST(@pID AS VARCHAR(11));
EXEC WriteLog 'Component', 'Source', @logMessage;

Exec statement syntax

For reference, the syntax of the EXEC statement is as follows:

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

Current Limitations

In SQL Server, acceptable values ​​for parameters in parameter lists are limited to literal values ​​and system functions prefixed with @@. Functions such as SCOPE_IDENTITY() are not supported.

The above is the detailed content of How Can I Pass Expression Results as Parameters to SQL Server Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!