Solved

Return one recordset as a result from Stored Procedure

Posted on 2004-10-09
5
440 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

695 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