Solved

Cursor definition using a call to Stored Procedure - SQL Server

Posted on 2011-09-16
3
452 Views
Last Modified: 2012-05-12
Hi,

Can i write the Cursor definition using a call to Stored Procedure instead of SELECT statement ? BTW: Stored procedure returns records (Same as SELECT statement)

Here is what i need:

declare C1 cursor for EXEC spSelPersonsDetails @StartPersonId, @EndPersonId, @Active

-- Continue with cursor

open C1

Thanks
0
Comment
Question by:milani_lucie
[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 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 36550546
no
 unless the stored procedure itself returns a cursor (cursor variable)

however you can call the stored procedure and have its result set inserted into a temp table and then process that.

insert into #temp (list of columns)
 exec yourprocedure @parem1,@paarm2,....


are you sure you need to process the results using a cursor ?
there are usually better(faster,less resource intensive) ways of processing a set of rows...

what do you need to do with the returned rows?
0
 

Author Comment

by:milani_lucie
ID: 36550639
I know that we can use WHILE loop instead of cursor. Are there any other ways to do ?

Thanks
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 250 total points
ID: 36551205
A cursor or while loop are both loops.  Loops should generally be avoided in SQL because SQL is designed to handle sets of data.  It's always best to employ set-based solutions instead of loops also known as RBAR(Row By Agonizing Row).  If you're inserting/updating/deleting data, you can probably handle all of the records at the same time  instead of one at a time.  If you're doing something like passing the values of each row to a stored procedure, you would have to loop, unless you can rewrite the stored procedure to handle a table of data via a user defined table type or XML.   Depends on what you are doing with the data.

Greg

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 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