Return one recordset as a result from Stored Procedure

Hello,
I need a sample stored procedure (for example on pubs or Northwind db).
I want to select from a table probably using cursor, create a temp recordset in stored procedure with some fields and in a loop do some process on each row of selected table and save them on temp recordset. Then return
the temp recordset as a result.

I programmed stored procedure three years ago and I forgotten synthax of declaration variable, cursor and ... but with using above sample I can solve my problem.

Thanks
-FA

LVL 14
Farzad AkbarnejadDeveloperAsked:
Who is Participating?
 
monosodiumgCommented:
That's a very vague questions. The syntax for a stored proc can be found in the BOL. An example:

create procedure foo(
 @arg varchar,
 @arg2 int = 1,
 @res1 varchar OUTPUT
)
AS
begin
 ... your code...
end

The syntac for cursor declarations can also be found in the BOL. A simple (adapted) example ripped straight from the BOL:
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT " "
   SELECT @message = "----- Books by Author: " +
      @au_fname + " " + @au_lname

   PRINT @message
   FETCH NEXT FROM authors_cursor
   INTO @au_id, @au_fname, @au_lname
END


There are no "recordsets" in SQL server. You can tables. YOu can have permananent tables, tables as local variables and temporary tables. You can return table objects from functions.

You probably do not want a cursor. Cursors are genrally avoided if at all possible.

You really need to supply a bit nore detail as to what you want.
0
 
Farzad AkbarnejadDeveloperAuthor Commented:
Hello,
Thanks a lot. I used temp table. What I need is returning my custom recordset (table). I wrote the following sp:

CREATE PROCEDURE [spRecordset] AS

declare @fname varchar(40)
declare @lname varchar(20)
declare @tmpPhone char(12)
declare @tmpAddress varchar(40)
declare @tmpCity varchar(20)
declare @name varchar(60)

CREATE TABLE  #TempTable
  (ColA varchar(40),
   ColB varchar(20),
   ColC varchar(60),
   ColD char(12),
   ColE varchar(20)
   )

declare c_authors cursor for
select au_fname, au_lname, phone, address, city from authors

open c_authors
 fetch next from c_authors into @fname, @lname, @tmpPhone, @tmpAddress, @tmpCity
while @@fetch_status = 0
begin
      set @name = @fname + ' ' + @lname
      insert into #TempTable  values(@fname,      @lname, @name, @tmpPhone, @tmpCity)
      fetch next from c_authors into @fname, @lname, @tmpPhone, @tmpAddress, @tmpCity
end

close c_authors
deallocate c_authors

select * from #TempTable
GO

-- *********************************************************

Thanks
-FA
0
 
monosodiumgCommented:
As I sad you REALLY REALLY shold avoid cursors if you can. Cursors are evil. They will make your hair fall out, they cause global warming and they make you really unnattractive to the opposite sex. Oh, and they also slow your database down.

What you are doing with cursors can be done in a single SQL statement:

insert into #TempTable  (ColA, ColB, ColC, ColD, ColE)
select au_fname , au_lname, au_fname + ' ' + au_lname, phone, city from authors

That's it. That will be much much faster.

You can go a step further an avoid the temp table:
CREATE PROCEDURE [spRecordset] AS
BEGIN
select au_fname , au_lname, au_fname + ' ' + au_lname, phone, city from authors
END
GO

That will be even faster for you and for anyone else using the same server.

0
 
Farzad AkbarnejadDeveloperAuthor Commented:
Thanks. But the above code is an sample. I need to walk through in data on each row and do some calculation on each row and some rows before current row and save them in a new recordset and return it.

Thanks
-FA
0
 
monosodiumgCommented:
There are indeed some situations where you are better off using a cursor. They are in my experience quite rare. YOu might be surprised at how much can be done with just SQL.  In fact, I think everything can be done with straight SQL but very occasionally cursors are more efficient. With SQL2K you also get to use UDF's which can simplify a lot of code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.