Solved

Return one recordset as a result from Stored Procedure

Posted on 2004-10-09
5
391 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

762 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

10 Experts available now in Live!

Get 1:1 Help Now