Dynamic SQL - Picking the values from the temp table

Question

I have a temp table that has the following information.

TableName ColumnName PrimaryKeyColumnName Primarykey(A GUID)

(4 columns)

I need to include one more new column Value that should grab data based on the condition below

"for each row in the temp table"

select ColumnName from  TableName  where PrimaryKeyColumnName  = Primarykey  

To be even more precise , the query must retrieve the values from the table directly.

I am sure this can be achieved using cursor. But it might impact my query performance. Could this be achieved using sp_sqlexecute? I tried converting the query as a varchar.

Like this

set @sql = 'select '+ #final.[primary field] +'from ' + #final.tablename +
                'where '+ #final.PrimaryKeyColumnName  + '='+ #final.Primarykey  

exec sp_sqlexecute  @sql

Answer 1

Possible this be helpful for you -

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp
GO

CREATE TABLE #temp
(
    [primary field] SYSNAME,
    tablename SYSNAME,
    PrimaryKeyColumnName SYSNAME,
    Primarykey SYSNAME
)

INSERT INTO #temp ([primary field], tablename, PrimaryKeyColumnName, Primarykey) 
VALUES 
    ('[column1]','[table1]','[column3]', '[column12]'), 
    ('[column2]','[table2]','[column4]', '[column24]')


DECLARE @SQL NVARCHAR(MAX)
SET @SQL = (
    SELECT CHAR(13) + 'SELECT '+ [primary field] + ' FROM ' + tablename +
        ' WHERE '+ PrimaryKeyColumnName  + ' = '+ Primarykey 
    FROM #temp
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
--EXEC sys.sp_executesql @SQL

Output -

SELECT [column1] FROM [table1] WHERE [column3] = [column12]
SELECT [column2] FROM [table2] WHERE [column4] = [column24]