How can I make this query accept dynamic table names?

Question

This is my function, which basically combines all the row data into one row. I know that the Coallasce function is available, but only out of curiosity I want to know how I can change this function to dynamically accept table names. Currently, it is read only from the Employee table.

ALTER FUNCTION [dbo].[ConcatStrig] ( @TableName varchar(64), @FieldName varchar(64) ) RETURNS varchar(max) AS BEGIN Declare @Sql as varchar(max) = '' Set @Sql = 'Select ' + @FieldName + ' from ' + @TableName Declare curTemp Cursor For Select EmpName from sp_executesql(@Sql) Declare @StrTemp as varchar(max) Declare @String as varchar(max) = '' Open curTemp Fetch Next from curTemp into @StrTemp While @@Fetch_Status = 0 Begin Set @String = @String + ' ' + @StrTemp Fetch Next from curTemp into @StrTemp End Close curTemp Deallocate curTemp Return @String END 

Thank you in advance:)

Answer 1

You will need to use dynamic SQL .

This is the only way to parameterize table names.

Answer 2

This is not exactly what you are looking for, but it may indicate the right direction. This uses dynamic sql and a neat concatenation trick in For XML Path ('').

 declare @SQL nvarchar(max), @TableName nvarchar(max) set @TableName='dbo.vwAsset' set @SQL=(select 'cast(isnull('+name+','''') as nvarchar)+'' ''+' from sys.columns where object_id=object_id(@TableName) for XML Path('') ) set @SQL=LEFT(@SQL,LEN(@SQL)-1) set @SQL='select ' +@SQL +' from ' +@TableName exec sp_ExecuteSQL @SQL,N'' 

Hope this helps!