How can I turn this dynamic query into a procedure or function?

Question

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)

Answer 1

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