CODEWITHSUNDEEP
sqlsql-serversybase
user3921265
user3921265

Reputation:

how to return one result set from procedure

I am trying to make a procedure return randomly selected rows using and old version of Sybase.

This is what I have come up with until now. It seems that each row is returned in its own result set.

How can I "group" all selected rows in a single result set?

create procedure samplerecords
    @pcttosample float as

    declare @val varchar(255)
    declare @cointoss float

    declare curs cursor for
         select foo from bar
         at isolation read uncommitted

    open curs

    fetch curs into @val

    while (@@sqlstatus != 2)
    begin

        select @cointoss=rand()*100
        if @cointoss <= @pcttosample
            select @val

        fetch curs into @val
    end
    close curs

return

Upvotes: 0

Views: 71

Answers (1)

shawnt00
shawnt00

Reputation: 17925

If you need to stick with the approach you're using with the rand() function then just insert the list of values into a temp table and then select them all together at the end.

create table #t (val varchar(255) not null) -- add at the beginning
...
insert into #t (val) values (@val) -- replaces the select @val
...
select val from #t -- add at the end

Upvotes: 1

Related Questions