Using the result of an [removed]e.g. Function call

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

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;

Answer 2

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

Answer 3

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

Answer 4

DECLARE @id int

SET @id = 10

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

Answer 5

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)

Answer 6

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;