CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
TheLovelySausage
TheLovelySausage

Reputation: 4094

TSQL Procedure returning multiple rows of data

I'm busy trying to rewrite some PostgreSQL stored procedures/functions for SQL Server 2014s TSQL.

I am struggling to return my values from this stored procedure though, this one is just a test but I am trying to return multiple rows of data in this case the for the two variables si_code and co_desc.

I have my procedure as follows (as a test)

if (object_id('p_get_serial')) is not null
   drop procedure p_get_serial
go

create procedure p_get_serial(@par01 char(20), @par02 integer)
as

   declare
      @co_num integer,
      @co_desc char(20),
       @si_code char(20),
       @log char(40)

   declare mycur cursor for
      select co_num, co_desc
       from colours
       where co_num <= @par02

   open mycur
   fetch next from mycur into @co_num,
                              @co_desc

   while @@FETCH_STATUS = 0
   begin

      set @si_code = ''

    select @si_code = si_code
       from sitems
       where si_co_num = @co_num

       set @log = @co_desc + ' ' + @si_code
       raiserror(@log,0,1) with nowait

      fetch next from mycur into @co_num, @co_desc

   end
   close mycur deallocate mycur

go

exec p_get_serial @par01 = 'paramater01', @par02 = 10

what is the best way to return my results knowing that there will be several rows?

Upvotes: 1

Views: 994

Answers (2)

Senthil_Arun
Senthil_Arun

Reputation: 1078

All you need to do is, just save the data for each row in a temp table or table variable and just write a SELECT statement at the the end of the Stored Procedure. Your question is not clear what you need exactly, you have a cursor and while loop, they seem to be redundant

Upvotes: 0

ajeh
ajeh

Reputation: 2784

In T-SQL you do not need to declare a cursor. Just select what you need and it will be available to the client app.

Cursor is Oracle/DB2/PostgreSQL etc way of returning data. SQL Server does not need it.

create procedure p
as
    select 1 as a

returns a recordset containing one record with one column.

create procedure p
as
    select 1 as a, 'a' as b
    union select 2, 'b'

returns two rows each with two columns.

Example of a more complex processing before returning a result set:

create procedure p
as
begin
    declare @a int, @b varchar(10)

    select @a = 1
    select @b = convert(varchar(10), @a)

    select @a = @a + 1

    select @a as a, @b as b -- this will be the resultset returned to the client
end

Upvotes: 1

Related Questions