Click here to Skip to main content
15,905,914 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I constantly getting error
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@SQLID".

The procedure is as follows:

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[WriteToLog](@Exe_module NVARCHAR(MAX) , @SQLID NVARCHAR(MAX) , @InputMode as CHAR(1) = 'I' )
    AS
    BEGIN

    DECLARE @SQL AS NVARCHAR(MAX)

    IF @InputMode = 'I'
    BEGIN

                    SET @SQL = 'INSERT INTO ExecutionLog (As_of_date, Exe_module, SqlId,TimeFrom)
                    VALUES (dbo.GetParameters(1012),Exe_module,@SQLID,GETDATE())';

                    EXECUTE sp_executesql @SQL;
    END
    ELSE

            SET @SQL = '    UPDATE O  SET  TimeTo = GETDATE(), Status = ''F''
                            FROM  ExecutionLog O
                    JOIN
                            (
                                    SELECT
                                            MAX(ID) ID--,  ROW_NUMBER() OVER (PARTITION BY  ID ORDER BY ID DESC) AS rn
                                    FROM
                                    ExecutionLog --Group by id
                            ) S ON O.id = S.id '


END;


The same is called from another procedure as follows
EXECUTE dbo.WriteToLog @Exe_module = 'Truncating ExecutionLog table', @SQLID = @sSQL, @InputMode = 'I'
Please if someone can help me solve the problem. What is wrong with this procedure?
Thanks in advance
A
Posted
Comments
vishal.shimpi 18-Mar-13 9:57am    
i thing your SqlId is integer field and you are trying to insert nvarchar value in it, make your @SQLID parameter as integer...

The error is correct. @SQLID does not exist in the context you are trying to use it. You are using it inside of a string, when what you actually need to do is use its value inside your string. This should work.


SQL
SET @SQL = 'INSERT INTO ExecutionLog (As_of_date, Exe_module, SqlId,TimeFrom)
                    VALUES (dbo.GetParameters(1012),Exe_module,' + @SQLID + ',GETDATE())';
 
Share this answer
 
SQL
SET @SQL = 'INSERT INTO ExecutionLog (As_of_date, Exe_module, SqlId,TimeFrom)
VALUES (dbo.GetParameters(1012),Exe_module,' + @SQLID + ',GETDATE())';
 
Share this answer
 
v2
The problem is "@SQLID" is a parameter variable not a string, so you need to do like...
SQL
SET @SQL = 'INSERT INTO ExecutionLog (As_of_date, Exe_module, SqlId,TimeFrom)
            VALUES (dbo.GetParameters(1012), '+ @Exe_module +', '+ @SQLID +', GETDATE())';

I have also added the @Exe_module, which I guess you have forgot and written like Exe_module and that is incorrect. It is also one parameter, which you should use in this query.

Thanks...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900