sql server - t-sql udf to return concatenated field values for one row in any given table -

i'm looking create function table name passed, , id value, , procedure returns single string field values specified row. tables referenced have field called 'id'. i've found other examples can concatenate specified row in table, have been unable find way dynamically specify table. below closest exampel found here :

tsql: values of field in row 1 string

create function udf_gettablevalues (@tablename varchar(max) = '',@id int = 0)     returns varchar(max) begin   declare @returnval varchar(max)    select @returnval = ''   select @returnval = @returnval           + ' // '           + t2.n.value('local-name(.)', 'nvarchar(128)')          +': '          + t2.n.value('.', 'nvarchar(max)')     (select *             branch             id = 1              xml path(''), type) t1(x)    cross apply t1.x.nodes('/*') t2(n)     return @returnval   end 

well first off, have come grips fact sql server refuses allow dynamic sql in functions. it's unfortunate, limitation must work around.

here technique apply dynamic sql logic on each row of table

• create temp table hold input parameters dynamic sql expected results
• create stored procedure expects temp table exist , loops on rows of temp table applying dynamic sql logic
• add data want evaluated temp table
• execute dynamic sql proc
• enjoy expected results :-)

1) let's start setting test data.

    -- ====================================================================     -- begin: setup test data     -- ====================================================================     if object_id('branch', 'u') not null drop table branch;     if object_id('branch2', 'u') not null drop table branch2;     go     create table branch (         id      int        ,column1 varchar(20)        ,column2 varchar(20)        ,column3 varchar(20)     )     create table branch2 (         id      int        ,column1 varchar(20)        ,column2 varchar(20)        ,column3 varchar(20)     )     set nocount on     insert branch select 1, 'hello', 'world', 'i write sql'     insert branch select 2, 'i like', 'it be', 'complicated'      insert branch2 select 1, 'hello', 'world', 'i write sql'     insert branch2 select 2, 'i like', 'it be', 'complicated'     set nocount off     go     -- ====================================================================     -- end: setup test data     -- ==================================================================== 

2) next, we'll create proc handles dynamic sql.

    -- ====================================================================     -- begin: proc dynamic sql logic     -- ====================================================================     if object_id('prcreturntabledata', 'p') not null drop procedure prcreturntabledata;     go     create procedure prcreturntabledata           declare @sql                    nvarchar(max)            ,@parameterdefinitions   nvarchar(max)            ,@tablename              varchar(128)            ,@id                     int      select @parameterdefinitions   = '@id int'            declare @idx int      select @idx = min(idx) #result      while @idx not null       begin         select @tablename = tablename               ,@id        = id           #result          idx = @idx          select @sql = '             declare @returnval varchar(max)              select @returnval = ''''             select @returnval = @returnval                    + '' // ''                    + t2.n.value(''local-name(.)'', ''nvarchar(128)'')                   + '': ''                   + t2.n.value(''.'', ''nvarchar(max)'')               (                     select *                       ' + @tablename + '                       id = @id                        xml path(''''), type                     ) t1(x)              cross apply t1.x.nodes(''/*'') t2(n)                update #result set returnval = @returnval id = @id';          exec sp_executesql @sql, @parameterdefinitions, @id = @id;          select @idx = min(idx) #result idx > @idx     end     go     -- ====================================================================     -- end: proc dynamic sql logic     -- ==================================================================== 

3) finally, let's execute proc , results want.

    -- ====================================================================     -- begin: solution execute dynamic sql logic on each row of table     -- ====================================================================     -- create temp table dynamic sql proc expects     if object_id('tempdb..#result', 'u') not null drop table #result;     create table #result (idx int identity(1,1), tablename varchar(128) not null, id int not null, returnval varchar(max) null);      -- popluate temp table rows want evaluate     insert #result (tablename, id)     select 'branch', id branch     insert #result (tablename, id)     select 'branch2', id branch2      -- results before     select * #result      -- execute dynamic sql proc     exec prcreturntabledata      -- results after     select * #result     -- ====================================================================     -- end: solution execute dynamic sql logic on each row of table     -- ====================================================================