T-SQL function to return more than 1 value

Question

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

So you do want only ONE value returned, you just want it to have different content? Besides, why do you want to return a string with the columns instead of the column names themselves? Who is going to use the string? If you call this from code, it's far easier to do string manipulation in code — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 1, 2013 at 12:26
No I need all the columns separated by comma.I am using those columns In another script(Create statistics) from where I am calling the function . — user2815115
– user2815115, Commented Nov 1, 2013 at 12:30
SQL Server has two data structures that are intended to be used yo contain multiple pieces of data. These are a) the table, and b) XML. A string containing comma separated values isn't on this list. Yes, you can create one. But you really shouldn't. It's not a natural representation for such data. How is the output of this function consumed? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 1, 2013 at 13:22

t-clausen.dk · Accepted Answer · 2013-11-01 14:07:04Z

3

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.

edited Nov 1, 2013 at 14:07

answered Nov 1, 2013 at 12:42

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2815115 Over a year ago

thanks t-clausen.dk for your solution..But ur function just returns the last column...There are more than 5 columns and it fetches just the last column

t-clausen.dk Over a year ago

@user2815115 I suspect that will be because of your restriction: and stats_id=@stats_id. Are you sure that is a restriction you need ? It will return more columns if you remove that part

Eduardo Molteni · Accepted Answer · 2013-11-01 12:44:45Z

0

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

answered Nov 1, 2013 at 12:44

Eduardo Molteni

39.5k25 gold badges145 silver badges212 bronze badges

1 Comment

user2815115 Over a year ago

thx all for your help..I got the required output by using the stuff and FOR XML PATH ........thx again for all ur valuable suggessions

Collectives™ on Stack Overflow

T-SQL function to return more than 1 value

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related