SQLTeam.com | Weblogs | Forums

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

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???

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