CODEWITHSUNDEEP
sqlsql-server
user2643021
user2643021

Reputation: 81

Using a variable through a Query located in a table

Basically I have a procedure that is supposed to take queries from a table and run them. This works fine except when I try to define a variable in the procedure that is referenced in the query. I simply define the variable using: 

DECLARE @spcode as varchar(255)
SET @spcode = 'C'

And then in the table I reference it here:

...
where sp_flag = @spcode
...

My procedure then runs through the table and executes all of the queries in the table, this works if I simply set sp_flag = 'C', but if I try to set it to the variable defined in the procedure I get the following error:

Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@spcode".

I have searched around but I have not been able to find a solution to this problem, perhaps someone has an idea of how I would go about fixing this problem?

Thanks,

Sam

Upvotes: 1

Views: 69

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7989

Look at this example.

First snippet execute SQL statement without using variable. Second with.

create table tbl1 (id int identity(1,1), value varchar(100));  
insert into tbl1 (value) values ('abc'), ('def'), ('xyz');

-- first snippet (without using variable)
declare
    @sql varchar(max);

set @sql = 'select * from tbl1 where value = ''abc''';

exec(@sql);



-- second snippet (with variable)
declare
    @sql nvarchar(max),
    @param nvarchar(100);

set @param = N'@val varchar(100)'
set @sql = N'select * from tbl1 where value = @val';

exec sp_executesql @sql, @param, @val = 'abc';

Upvotes: 2

Related Questions