Solved

Return one recordset as a result from Stored Procedure

Posted on 2004-10-09
5
407 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Farzad Akbarnejad
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
monosodiumg earned 500 total points
ID: 12265831
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
 
LVL 14

Author Comment

by:Farzad Akbarnejad
ID: 12265887
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12265959
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
 
LVL 14

Author Comment

by:Farzad Akbarnejad
ID: 12271650
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12272249
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now