sql-servert-sqldynamic-queries

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


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)

Solution

  • 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