Database

Member 8037172 wrote:

What technique(s) do you prefer?

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

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
*/

berba wrote:

Note: I don't want to convert it to varchar.

select cast(getdate() as date)