UnQuote a nvarchar() string and use it as a table name

reza assar

hi all suppose i have a table named "PlaceHolder" with this fields

ID int
TableName nvarchar
FieldName nvarchar

these fields will indicate specified table.field in my databse so i want to retrieve them and use it`s content as a tableName.fieldName in my select transaction

SELECT [SQL1].[NewFieldName]------> REPLACED WITH '[TableName].[FieldName]' RETREIEVED BY INNER SELECT FROM
(SELECT '['+[TableName]+']' + TableName + '.['+[FieldName]+']' NewFieldName
FROM [PlaceHolder]
WHERE ....
) AS SQL1
WHERE ...

it will show me the names of some [tables].[field] but i want it`s values in these fields

Lost User

Construct a dynamic query in a string and use EXECUTESQL to execute the query.

PIEBALDconsult

I prefer to use String.Format to construct something like that; all those quotes, plusses, and brackets get in the way of understanding what's going on. Otherwise it should work fine.

Niladri_Biswas

Hi try this Input:

Declare @tblPlaceholder table(ID int,TableName Nvarchar(20),FieldName Nvarchar(20))
insert into @tblPlaceholder
select 1,'spt_values','name' union all select 1,'spt_values','number' union all
select 2,'spt_monitor','lastrun' union all select 2,'spt_monitor','cpu_busy'
--select * from @tblPlaceholder

Query:

Declare @t table(ID int,Query VARCHAR(2000))
Declare @QueryList VARCHAR(2000)
Declare @i int
set @i = 1

-- Step 1: Build the query and insert the same into a table variable
Insert into @t
Select ID, Query = ' Select ' + FieldNames + ' from ' + TableName
From
(
Select ID,TableName, FieldNames = Stuff((select ',' + CAST(TableName as Nvarchar(20)) + '.' + CAST(FieldName as Nvarchar(20))
from @tblPlaceholder t2 where t2.ID = t1.ID
FOR XML PATH('')),1,1,'')
From @tblPlaceholder t1
Group By t1.ID,t1.TableName)X

/* output of step 1
select * from @t

ID Query
1 Select spt_values.name,spt_values.number from spt_values
2 Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
*/

-- Step 2 : loop thru the ID and execute the queries
While (@i <= 2) -- since there are two id's.. this can even be configurable as max(id)
Begin
SELECT @QueryList = (Select Query from @t where ID = @i)
exec(@QueryList)
set @i += 1
End

/* Final output

Output of Select spt\_values.name,spt\_values.number from spt\_values

name	number
(rpt)	-32768
YES OR NO	-1
SYSREMOTELOGINS TYPES	-1
SYSREMOTELOGINS TYPES (UPDATE)	-1


Output of Select spt\_monitor.lastrun,spt\_monitor.cpu\_busy from spt\_monitor

lastrun					cpu\_busy
2008-07-0916:46:13.877	10

*/

I have given the demonstration by using two system tables (spt_values and spt_monitor) found in the Master database. The comments about the steps are mentioned in the code itself. Hope this will help you Let me know in case of any concern. Thanks

Niladri Biswas

reza assar

hi thanx to ur answer it took a time fore me to understand what just going on i will try it and answer back :) :-D ;)