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

I am not sure that I understood what you are trying to do but try this:

declare @sql varchar(8000);
set @sql = null;
select @sql = 
coalesce(@sql+' union all select '+ColumnName+' from '+ tablename,  
'select '+ColumnName+' from '+ tablename) 
from final where ColumnName = PrimaryKeyColumnName;
exec sp_sqlexecute @sql;       

Notice that the @sql is limited -in my sample 8000- if the temp table you have is too big you gonna have to make a separate executes,needs some changes in the code. Always you have to set an initial value for @SQL as null. I think no need for cursor, this will be solved using some SQL tricks. Hope this will help...

Answer 2

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]

Answer 3

Create Table #tempValueFetch

(

TableName sysname,
PrimaryKeyColumnName sysname,
AttributeColumnName sysname,
Primaryfield sysname,
PrimaryKey sysname,
Label nvarchar(max),
Value nvarchar(max)

)

this temp table has been populated with 5 columns namely TableName,PrimaryKeyColumnName,AttributeColumnName ,Primaryfield ,PrimaryKey

The columns label and value has to updated dynamically.Note that primary key is a uniqueidentifier