Solved

Return one recordset as a result from Stored Procedure

Posted on 2004-10-09
5
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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