CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlcasting
WorkerThread
WorkerThread

Reputation: 2213

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

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?

Upvotes: 19

Views: 21295

Answers (7)

live-love
live-love

Reputation: 52494

Use this code to print a Sql Server Error message:

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO

Here is the result set.

Copy
-----------

(0 row(s) affected)

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorLine  ErrorMessage
----------- ------------- ----------- --------------- ---------- ----------------------------------
8134        16            1           NULL            4          Divide by zero error encountered.

(1 row(s) affected)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453847

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

Upvotes: 19

Rahil Kidwai
Rahil Kidwai

Reputation: 9

DECLARE @id int

SET @id = 10

SELECT LTRIM(RTRIM(STR(@id))) AS stringValue

Upvotes: 0

codingbadger
codingbadger

Reputation: 44032

DECLARE @pID int;
declare @IdAsString varchar(100)

SET @pID = 1;

Select @IdAsString ='Could not find given id: ' + Cast(@pId as varchar(10))

EXEC WriteLog 'Component', 'Source', @IdAsString

As pointed out by Martin, the following only applies to columns not variables.

Note that I have amended your cast to varchar(10) this will allow for integers larger than 1 digit. varchar will only allow 1 character

Upvotes: 2

Lamak
Lamak

Reputation: 70668

You can't do operations on the parameters of a stored procedure. You should assign that value on another variable and then pass it to your SP.

DECLARE @pID int, @nameId VARCHAR(100);
SET @pID = 1;
SET @nameId = 'Could not find given id: ' + CAST(@pID AS varchar);

EXEC WriteLog 'Component', 'Source', @nameId

Upvotes: 1

Craig T
Craig T

Reputation: 1071

Try this instead...

DECLARE @pID int;
SET @pID = 1;

DECLARE @message varchar(255);
SET @message = 'Could not find given id: ' + CAST(@pID AS varchar)

EXEC WriteLog 'Component', 'Source', @message;

Upvotes: 0

Bruno Costa
Bruno Costa

Reputation: 2720

Perhaps something like this?

DECLARE @pID int;
SET @pID = 1;

DECLARE @Message NVARCHAR(50);

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

Upvotes: 0

Related Questions