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

by

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

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. Referring to a Column Alias in a WHERE Clause
  7. SQL query to select dates between two dates
  8. Convert Month Number to Month Name Function in SQL
  9. Create a date from day month and year with T-SQL
  10. Selecting COUNT(*) with DISTINCT
  11. How to return multiple values in one column (T-SQL)?
  12. Generate a resultset of incrementing dates in TSQL
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. Better techniques for trimming leading zeros in SQL Server?
  16. SQL – Subtracting a depleting value from rows
  17. How to print VARCHAR(MAX) using Print Statement?
  18. What’s the best way to select the minimum value from several columns?
  19. Concatenate values based on ID
  20. Splitting delimited values in a SQL column into multiple rows
  21. SELECT FOR UPDATE with SQL Server
  22. How to split string using delimiter char using T-SQL?
  23. OPENXML with xmlns:dt
  24. SQL server select distinct rows using most recent value only
  25. Eliminate and reduce overlapping date ranges
  26. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  27. Is there a quick way to check if ANY column is NULL?
  28. Why do SQL Server Scalar-valued functions get slower?
  29. SQL query for parent-child chain
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment