Dynamic SQL with loop through table rows with cursor in SQL Server

Question

I have two tables (#aaa_Test & #bbb_Test). I want to loop through each row of the #aaa_Test table.

I need to iterate every row 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.

These are 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 column name for #aaa*Test.* If you look at the below query closely, Hole, Project and source come from column name 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 #aaaTest 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;

Answer 1

You don't need a cursor for this and, since you're building the query from #aaa_Test anyway, you don't need to pull the length from the #temp table as a subquery in every row, either:

DECLARE @sql nvarchar(max) = N'SELECT ';

SELECT @sql += STRING_AGG(CONVERT
(
  nvarchar(max), 
  CONCAT
  (
    char(13), char(10),
    'CONCAT(',
    QUOTENAME(Columnname), 
    ',SPACE(',
    CONVERT(varchar(11), [Length]),
    '-LEN(', 
    QUOTENAME(Columnname), 
    '))) AS ',
    QUOTENAME(Columnname)
  )
), ',')
FROM #aaa_Test;

SET @sql += char(13) + char(10) + N' FROM #bbb_Test;';


SELECT @sql;

EXEC sys.sp_executesql @sql;

Output:

SELECT 
CONCAT([Hole],SPACE(18-LEN([Hole]))) AS [Hole],
CONCAT([Project],SPACE(8-LEN([Project]))) AS [Project],
CONCAT([Source],SPACE(10-LEN([Source]))) AS [Source]
FROM #bbb_Test;

Hole	Project	Source
08SWRB001	WDW	Droll
08SWRB002	WDWWDW	Gup
08SWRB003	WDWMD	Sh

• Example db<>fiddle