Dynamic SQL with Loop through each Table Rows using Cursor in SQL Server

Transact-SQL
1

I have two table tables(#aaa_Test & #bbb_Test). I want to do sql loop through each row of a #aaa_Test table. I need to iterate every rows of #aaa_Test using a cursor. Also, I have a dynamic SQL statement I've created in a stored procedure. I want to use dynamic SQL and cursor both together to generate my final query. I'm having a hard time figuring out the right way to approach this problem. Here's what I have done.

This is my two tables.

DROP TABLE IF EXISTS #aaa_Test;
	CREATE TABLE #aaa_Test (
		[Columnname] VARCHAR(500) NOT NULL,
		[Length] VARCHAR (500) NOT NULL
		);

INSERT INTO #aaa_Test
VALUES (
	'Hole',
	'18'
	)
	,(
	'Project'
	,'8'
	)
	,(
	'Source',
	'10'
	);

SELECT * FROM 	#aaa_Test;

DROP TABLE IF EXISTS #bbb_Test;
	CREATE TABLE #bbb_Test (
		[Hole] VARCHAR(500) NOT NULL,
		[Project] VARCHAR (500) NOT NULL,
		[source] VARCHAR (500) NOT NULL
		);

INSERT INTO #bbb_Test
VALUES (
	'08SWRB001'
	,'WDW'
	,'Droll'
	)
	,(
	'08SWRB002'
	,'WDWWDW'
	,'Gup'
	)
	,(
	'08SWRB003'
	,'WDWMD'
	,'Sh'
	);

SELECT * FROM 	#bbb_Test;

This is my final select query that I want in output(Desired Result) but I wanted dynamic as I have more than 1000 entries of columnname for #aaa Test. If you look at the below query closely, Hole, Project and source come from columnname of #aaa_test table. I want this column name dynamic rather than hard coding each column name as I have so many entries of column name in #aaa Test table .

SELECT CONCAT(Hole,SPACE((SELECT [Length] FROM #aaa_Test WHERE columnname='Hole') - LEN(Hole))) AS Hole
       ,CONCAT(Project,SPACE((SELECT [Length] FROM #aaa_Test WHERE columnname='Project') - LEN(project))) AS project    
       ,CONCAT([Source],SPACE((SELECT [Length] FROM #aaa_Test WHERE columnname='Source') - LEN([source]))) AS [source]
FROM #bbb_Test;

This is the combination of my Dynamic SQL AND Cursor.

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    SELECT * FROM #aaa_Test as a;     
OPEN @MyCursor; 
FETCH NEXT FROM @MyCursor 
INTO @MyField;

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @SQL VARCHAR(MAX);

  SET @SQL = '';

  SELECT @SQL
    = @SQL + ' SELECT Concat(' + QUOTENAME(a.[Columnname], '') + ',SPACE((SELECT [Length] from #aaa_Test where columnname = '+ QUOTENAME(a.[columnname], '''') +'-LEN(' + QUOTENAME(a.[Columnname],'') +' as '+ QUOTENAME(a.[Columnname],'')    
   FROM #bbb_Test AS b;

  PRINT @SQL;   
  
  FETCH NEXT FROM @MyCursor 
  INTO @MyField; 
END; 
Exec(@SQL);
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;

Any help how to solve this problem???

2

Before going down this loop rabbit hole, what is the final data you want look like? Dynamic query based on loop might not be the desired solution.

1 Like