How to Pass the Result of an Expression as a Parameter in a SQL Server Stored Procedure?

Passing expression result as parameter in SQL Server stored procedure

A developer encountered an error while trying to execute a stored procedure, aiming to improve database development. The error occurs when trying to pass the result of an expression (specifically the concatenation of the string "Could not find given id: " with the value of the integer variable @pID) directly into the argument list.

However, SQL Server does not support such operations in the parameter list itself. Instead, an intermediate variable must be used to hold the result of the expression before passing it to the argument.

The syntax of EXEC does not explicitly specify the format of the "value" in the parameter part, indicating that only simple expressions are accepted. These expressions are limited to literal values ​​and some system functions prefixed with @@. Other system functions, even those that do not require parentheses, are not allowed.

So, in order to resolve this error, developers need to use the following syntax:

<code class="language-sql">DECLARE @pID INT;

SET @pID = 1;

/*如果使用2008及以上版本，之前的版本需要分成两个单独的语句*/
DECLARE @string VARCHAR(50) = 'Could not find given id: ' + CAST(@pID AS VARCHAR(11));

EXEC WriteLog
  'Component',
  'Source',
  @string;</code>

The above is the detailed content of How to Pass the Result of an Expression as a Parameter in a SQL Server Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!