MNiGruag MNiGruag - 4 months ago 8
SQL Question

Looping through table records in SQL Server

I have a table as such:

Screenshot of table

Each record in the column

ColumnName
contains the name of a column in another table,
regressionDifferences
. I want to loop through
ColumnName
and select each of the columns in
regressionDifferences
and insert them into
ColumnContent
. At the moment I'm just focusing on printing the column content to the screen, and then I will worry about inserting it.
This is what I have written so far:

DECLARE @LoopCounter INT = 1, @MaxRowNumber INT = 42762,
@ColumnName VARCHAR(max), @UniqueID VARCHAR(max)

WHILE(@LoopCounter < @MaxRowNumber)

BEGIN

SELECT @ColumnName = ColumnName
FROM ColumnDifference WHERE RowNumber = @LoopCounter

SELECT @UniqueID = UniqueID
FROM ColumnDifference WHERE RowNumber = @LoopCounter

SELECT DISTINCT @ColumnName AS ColumnContent FROM regressionDifferences rD
WHERE rD.UniqueID LIKE @UniqueID + '%'

SET @LoopCounter = @LoopCounter + 1

END


For some reason this is just returning the names of the columns, ie
a_4, a_4, a_4, a_10, a_11, a_6
, etc as opposed to the actual content of each column.

Can anyone help me as to where I'm going wrong?

Thanks

Answer
declare @sql varchar(max);
set @sql ='SELECT DISTINCT ' + @ColumnName + 'AS ColumnContent FROM regressionDifferences rD WHERE rD.UniqueID LIKE ''' 
                       + @UniqueID + '%'''
exec(@sql);

use the above query to create dynamic sql you need.

your dynamic query is equivalent to

SELECT DISTINCT 'a_4' AS ColumnContent FROM regressionDifferences rD
WHERE rD.UniqueID LIKE '2016-07-26''+ '%'

The variable @ColumnName is not treated as column name in your dynamic query. It is treated as text

Comments