CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
user2815115
user2815115

Reputation: 79

T-SQL function to return more than 1 value

I have a T-SQL function to return all the associated columns of the statistics attribute I am passing the stats_id and object_id of sys.stats system table. I need all the columns separated by ',' but my function returns just the first column.Pls help I am working on Sql server 2005

create FUNCTION fGetstatscols (
    @objectid INT,
    @stats_id  INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @V_IncludedCol NVARCHAR(200)
    DECLARE collist CURSOR
    ( select (name) from sys.columns where Object_id =@objectid 
        and column_id in (select column_id from  sys.stats_columns where object_id=@objectid 
        and stats_id=@stats_id))   
    OPEN collist
    FETCH NEXT FROM collist INTO @V_IncludedCol
    WHILE @@FETCH_STATUS <> 0
    begin 
        Select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') 
    END
    close collist
    deallocate collist
    RETURN @V_IncludedCol
END

Upvotes: 0

Views: 563

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Avoid using cursor in this and most other cases.

CREATE FUNCTION fGetstatscols (
    @objectid INT,
    @stats_id  INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @V_IncludedCol NVARCHAR(200) = ''
    SELECT @V_IncludedCol += ','+name 
    FROM sys.columns c 
    WHERE Object_id =@objectid 
        and exists (SELECT 1 from  sys.stats_columns 
                    WHERE object_id=@objectid 
                     and stats_id=@stats_id
                     and c.column_id=column_id)
    RETURN stuff(@V_IncludedCol, 1,1, '')
END

You should be aware, that it is possible to let the function return a table with the results as well.

Upvotes: 3

Eduardo Molteni
Eduardo Molteni

Reputation: 39413

Every time the WHILE loops the @V_IncludedCol variable gets filled with the new value, that why you get only one value

create FUNCTION fGetstatscols (
    @objectid INT,
    @stats_id  INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @V_IncludedCol NVARCHAR(200)
    DECLARE @retval NVARCHAR(200)
    DECLARE collist CURSOR
    ( select (name) from sys.columns where Object_id =@objectid 
        and column_id in (select column_id from  sys.stats_columns where object_id=@objectid 
        and stats_id=@stats_id))   
    OPEN collist
    FETCH NEXT FROM collist INTO @V_IncludedCol
    WHILE @@FETCH_STATUS <> 0
    begin 
        if @retval is null 
            select @retval = @V_IncludedCol
        else 
            Select @retval = retval + ',' + @V_IncludedCol
    END
    close collist
    deallocate collist
    RETURN @retval
END

But you are better using t-clausen.dk solution, that avoids using a cursor

Upvotes: 0

Related Questions