CODEWITHSUNDEEP
sqlsql-server-2008
TCM
TCM

Reputation: 16920

How can I make this query to accept dynamic table names?

This is my function which mainly concats all the rows data into one string. I know a function named Coallasce is available, however just out of curiosity I want to know how can I change this function to accept table names dynamically. At present it only reads from 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

Thanks in advance:)

Upvotes: 0

Views: 1527

Answers (2)

Brent D
Brent D

Reputation: 908

This isn't quite exactly what you're looking for, but it might point you down the right direction. This uses dynamic sql and a neat trick for concatenation 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!

Upvotes: 1

Oded
Oded

Reputation: 499352

You will need to use dynamic SQL.

That's the only way to parameterize table names.

Upvotes: 2

Related Questions