Sql Declaring a variable in stored procedure

Question

i have created a stored procedure and in which i want to set a rowid and table name from select query to a variable but when iam executing my code in sql the bunch of errors are appears

i have decalre a variable but its keep saying to decalare table

heres My code which i have written

USE [DB_A4BA76_logistic]
GO
CREATE PROCEDURE RecoverLogs @id int
AS
declare @TableName nvarchar(50)
declare @RowId int
set @TableName= ( select Logs.tableName from logs where Logs.id=@id)
set @Rowid= ( select Logs.Rowid from logs where id=@id)
Update @TableName set UpdateStatus='0' where id = @Rowid
if(select UpdateStatus from @TableName where id = @Rowid) = '0'
select 1
else
select 2
GO;

and these error are appearing after executing the above code

Msg 1087, Level 16, State 1, Procedure RecoverLogs, Line 7
Must declare the table variable "@TableName".
Msg 1087, Level 16, State 1, Procedure RecoverLogs, Line 8
Must declare the table variable "@TableName".

You can't use a variable/expression to replace the value of an object; it must be a literal. You'll have to use dynamic SQL here. — Thom A
– Thom A ♦, Commented Sep 2, 2019 at 10:32
How do you SELECT....INTO with a dynamic object, @AmiraBedhiafi? — Thom A
– Thom A ♦, Commented Sep 2, 2019 at 10:35
i used Select .... into but still error appears SELECT Logs.tableName INTO @TableName FROM logs WHERE Logs.id=50 following errors Appears Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '@TableName'. Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@TableName". — Moiez Hussain
– Moiez Hussain, Commented Sep 2, 2019 at 10:44

Thom A · Accepted Answer · 2019-09-02 10:44:10Z

2

This is a bit of a stab in the dark, and makes some assumptions, but I suspect this is what you want. Note I changed the SELECT to an OUTPUT parameter, as that seemed like a better fit here as well.

CREATE PROC dbo.RecoverLogs @id int, @status tinyint OUTPUT
AS BEGIN



    DECLARE @SQL nvarchar(MAX);

    SELECT @SQL = N'DECLARE @RI table (UpdateStatus int);' + NCHAR(13) + NCHAR(10) +
                  N'UPDATE dbo.' + QUOTENAME(tableName) + NCHAR(13) + NCHAR(10) +
                  N'SET UpdateStatus = 0' + NCHAR(13) + NCHAR(10) +
                  N'OUTPUT inserted.UpdateStatus' + NCHAR(13) + NCHAR(10) +
                  N'INTO @RI' + NCHAR(13) + NCHAR(10) +
                  N'WHERE id = ' + QUOTENAME(Rowid,'''') + N';' + NCHAR(13) + NCHAR(10) +
                  N'SELECT @status = CASE WHE UpdateStatus = 0 THEN 1 ELSE 2 END' + NCHAR(13) + NCHAR(10) +
                  N'FROM @RI;'
    FROM dbo.logs
    WHERE id = @id;

   PRINT @SQL; --Your best friend
   EXEC sp_executesql @SQL, N'@id int, @status tinyint OUTPUT', @id, @status OUTPUT;
END;

If you get any errors, use your best friend to help you. Otherwise post some sample data and expected results in your question, so that we can test (as we can't right now).

answered Sep 2, 2019 at 10:44

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Moiez Hussain Over a year ago

Youre a LifeSave Man Thankyou Soooooo Much It Works As I want <3

Thom A Over a year ago

The important thing is, @MoiezHussain, is do you udnerstand it?

Moiez Hussain Over a year ago

honestly speaking ! i didnt understand it but iam happy that it works for me and resulting output as i want XD

Thom A Over a year ago

Taking the time to understand it is important. This explains some of the logic behind what I've done here: Dos and Don'ts of Dynamic SQL however, if there are parts you needs explaining please do ask. At the end of the day it's you who needs to be able to support the above code; not me or anyone else on Stack Overflow.

Collectives™ on Stack Overflow

Sql Declaring a variable in stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related