returning multiple values through an output parameter

21 Jul 2005 6:46 PM

jason

given the following table:

create table t1 (
id int,
description varchar(50)
)

and the following stored procedure:

create procedure owner.proc1 (
@input1 int,
@input2 string,
@output1 int output,
@output2 string output)
as

-- unrelated stuff

select @output1 = count(*) from t1 where description = @intput2
go

my question is: what is the most sane way to to change this stored
procedure to return a list of id's in output2, instead of just the
count in output1.

there is a lot of other stuff going on in the procedure that prevents
it from just returning a recordset. because of this, i'm curious how to
pass multiple values through an output parameter.

from what i've read on this thread, there is no SQL array option, so
most people recommend a comma-delimited string. if that's the case, can
someone show me how to build a select statement to get a comma
separated string of id's from t1 where description matches intput2?

thanks for any help,

jason

21 Jul 2005 7:12 PM

ML

What in the world could be going on in a procedure that would prevent it from
returning a resultset?

Concatenated values are generally a bad idea, but if you insist...
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

ML

21 Jul 2005 7:15 PM

Barry

Jason,

It's not pretty but I think it will work.

declare @s varchar(8000),
    @i int,
    @rv varchar(10),
    @rowcount int,
    @r int

Set @i = 0

declare c cursor for

Select ID from t1 where Description = @input2

Set @rowcount = (Select Count(*) from t1 where Description = @input2)

open c

fetch next from c into @rv

while @@fetch_status = 0

    begin
        if @i = 0

            Begin

                Set @s = @rv + ',' + space (0)
                Set @i = 1
                Set @r = 1

            end

        Else
            Begin

                If @r <> (@rowcount -1)

                    Begin

                        Set @s = @s + @rv + ',' + space(0)
                        Set @r = @r + 1

                    End

                Else
                    Begin

                        Set @s = @s + @rv

                    End
            End

    Fetch next from c into @rv

    End

close c
deallocate c

select @s

Hope this helps

Thanks

Barry

21 Jul 2005 8:19 PM

jason

thanks so much for the direction! worked like a charm.