CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Pran
Pran

Reputation: 155

How to add string in sql

Hello my code is as follows: 

DECLARE @cnt INT = 0;
WHILE @cnt < 256
BEGIN
SELECT
ITEM1 ,
ITEM2 ,
ITEM3
FROM Table_Name
SET @cnt = @cnt + 3
End

I want to do like below:

DECLARE @cnt INT = 0;
WHILE @cnt < 256
BEGIN
SELECT
ITEM@cnt+1 ,
ITEM@cnt+2 ,
ITEM@cnt+3
FROM Table_Name
SET @cnt = @cnt + 3
End

So that it will be executed in a single loop. Please help me regarding this.

Thank you in advance

Upvotes: 0

Views: 53

Answers (1)

Andrea
Andrea

Reputation: 12405

If you want to compose your query using variables, you must use dynamic TSQL to construct your select, then you can execute it with EXEC(...):

DECLARE @sql varchar(max)= ' SELECT '
DECLARE @cnt INT = 0;

WHILE @cnt < 256
    BEGIN
        set @cnt = @cnt + 1
        set @sql = @sql + ' ITEM' + cast(@cnt as varchar(max)) + ',' 
    End
set @sql = replace (@sql + ' from #table_name',', from',' from')

exec (@sql)

Upvotes: 1

Related Questions