I have a dynamic query that pulls from a list of tables with the names of those stored in another table but I would like to be able to use the resulting set in another query.
declare @t table( tablename varchar(50))
declare @sql varchar(max)
set @sql = ''
insert into @t
SELECT t.svc_table AS table_name FROM svc_defs AS t
SELECT @sql = @sql + 'Select convert(varchar(5),svc_defs.svc_id) as svcid, data_id, crid, d_custid, d_active From ' + tablename +
' inner join svc_defs on svc_defs.svc_table = ' + '''' + tablename + '''' + ' union ' from @t
--remove the trailing 'union'
Select @sql = substring(@sql, 1, len(@sql) - 6)
exec (@sql)
You can create scalar user defined function, which returns the sql statement.
CREATE FUNCTION dbo.udf_GenerateSelectQuery()
Returns nvarchar(max)
AS
BEGIN
declare @t table( tablename SYSNAME)
declare @sql Nvarchar(max)
set @sql = ''
insert into @t
SELECT t.TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES AS t
SELECT @sql = @sql + 'Select convert(varchar(5),svc_defs.svc_id) as svcid, data_id, crid, d_custid, d_active From ' + tablename +
' inner join svc_defs on svc_defs.svc_table = ' + '''' + tablename + '''' + ' union ' from @t
--remove the trailing 'union'
Select @sql = substring(@sql, 1, len(@sql) - 6)
RETURN @sql
END
you can call it as
declare @sqlstmt NVARCHAR(max) = dbo.udf_GenerateSelectQuery()
SELECT @sqlstmt
or as
declare @sqlstmt NVARCHAR(max)
SET @sqlstmt = (SELECT dbo.udf_GenerateSelectQuery())
SELECT @sqlstmt