CODEWITHSUNDEEP
sqlsql-servervariablesdynamic
Gergő Barta
Gergő Barta

Reputation: 149

Variable in a dynamic query

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?

Upvotes: 0

Views: 801

Answers (4)

user3884116
user3884116

Reputation: 9

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

Upvotes: 0

Dave Evans
Dave Evans

Reputation: 26

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;

Upvotes: 0

gotqn
gotqn

Reputation: 43626

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

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

sp_executsql documentation

Upvotes: 0

mohan111
mohan111

Reputation: 8865

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

change like this may it works

Convert(nvarchar(2), @j);

Upvotes: 2

Related Questions