Variable in a dynamic query

Question

I have a problem once I want to run the next query:

declare @j int = 1;

declare @column varchar(255);
set @column = 'last_name';

declare @tmp varchar(255); 
declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id = @j'

declare @tbl table(tmp varchar(255)) 

insert into @tbl 
exec sp_executesql @query

select top 1 @tmp = tmp from @tbl 

select @tmp

select * from @tbl;

The problem is that if I change the variable @j to a numeric value in the declaration of the @query variable, like this

declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id = 1'

the query is running successfully, if I left the @j variable there, like this

declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id = @j'

I got an error message:

"Must declare the scalar variable @j."

Why? And how can I solve that my query would work with the variable @j?

Why do you have that inconsistency in concatenation - @column is concatenated without quotes and @j is within quotes? — Kitet
– Kitet, Commented Jul 30, 2014 at 9:34

mohan111 · Accepted Answer · 2014-07-30 09:40:09Z

2

in the place of ==== where id = @j'

change like this may it works

Convert(nvarchar(2), @j);

edited Jul 30, 2014 at 9:40

answered Jul 30, 2014 at 9:31

mohan111

8,8854 gold badges35 silver badges61 bronze badges

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

Comments

gotqn · Accepted Answer · 2014-07-30 09:40:13Z

You need to cast the @j variable to string. AS its type is INT you should cast it as

CAST(@j AS VARCHAR(12))

Also, you can pass parameters to dynamic SQL statement when it is executed using sp_executesql. In your case it will be something like this:

declare @j int = 1;

declare @column varchar(255);
set @column = 'last_name';

declare @tmp varchar(255); 
declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id = @j'

declare @tbl table(tmp varchar(255)) 

insert into @tbl 
exec sp_executesql @query, N'@j INT', @j = @j

select top 1 @tmp = tmp from @tbl 

select @tmp

select * from @tbl;

In the following line:

exec sp_executesql @query, N'@j INT', @j = @j

@query is your dynamic T-SQL statement
N'@j INT' is declaration of parameters
@j = @j is parameters assignments

sp_executsql documentation

Dave Evans · Accepted Answer · 2014-07-30 13:40:26Z

0

You can use parameters in the sp_executesql statement like this:

CREATE TABLE TempTable (
    TempID INT IDENTITY(1,1) NOT NULL,
    SomeDescription VARCHAR(255) NOT NULL,
    PRIMARY KEY(TempID))

INSERT INTO TempTable (SomeDescription) 
VALUES ('Description 1'),
    ('Description 2'),
    ('Description 3')

DECLARE @sql NVARCHAR(500) = 'SELECT * FROM TempTable WHERE TempID = @TempVar',
        @params NVARCHAR(500) = '@TempVar int',
        @j INT = 2;

EXEC sp_executesql @sql, @params, @TempVar = @j;

answered Jul 30, 2014 at 13:40

Dave Evans

264 bronze badges

Comments

user3884116 · Accepted Answer · 2014-07-30 15:51:01Z

0

declare @j int = 1;

declare @column varchar(255); set @column = 'last_name';

declare @tmp varchar(255); declare @query nvarchar(255) = N'select ' + @column + N' from TEST where id ='+ CAST(@j AS VARCHAR(5))

declare @tbl table(tmp varchar(255))

insert into @tbl exec (@query)

select top 1 @tmp = tmp from @tbl

select @tmp

select * from @tbl;

enter code here

answered Jul 30, 2014 at 15:51

user3884116

91 bronze badge

Collectives™ on Stack Overflow

Variable in a dynamic query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related