Cursor in SP

Question

Cursor in SP

Chiranjib Chowdhury

Old Hand

Points: 326
More actions
July 18, 2005 at 12:32 am

#90312

Hi all,
If a SP is calling a second SP is it possible to access the cursor used in the first SP in the second SP as well? Specify with code example.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Chiranjib - I know that you can pass the results of the main SP as parameters to the second one as you're looping through.....is this what you mean ?!

ps: We've already been through this in the other post so I'm not going to say what I'm sure someone else will on the usage of cursors! (Pax!)

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 2

>> ps: We've already been through this in the other post so I'm not going to say what I'm sure someone else will on the usage of cursors! <<

And that will be ... ME

Chiranjib - Can you post what are you trying to accomplish with the cursors so that we may have a shot to a SET-BASED solution instead

Cheers

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Chiranjib - you were warned in the other post that the cursorsAreEvil people will "talk" to you... - well, here's one already - Remi's on vacation else he'd have been the second (or first depending on typing speed

**ASCII stupid question, get a stupid ANSI !!!**

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 4

I don't believe you can pass a cursor as a parameter to a stored procedure. You can, however, pass the results of each loop of the cursor to another stored procedure. I am one of those "Cursors Are Evil" people. I use them only for small datasets (Less than 100 records) that are not regular processes. Please post a sample of your code and data set so we can give you some better direction. As Noel mentioned, there may be a set based method to accomplish what you wish.

noeld SSC Guru Points: 96590 More actions · Answer 5

I just want to point out that it is possible to pass cursor data type as a parameter.

From BOL:

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

........................................

data_type

Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

Now I am going to try to disuade you from following that path. Please Try to look at it Set-BASED

* Noel

BarryOC SSC Veteran Points: 299 More actions · Answer 6

Hello Chiranjib Chowdhury,

Obviously the objection to cursors is performance. Now when I picture passing the results of each loop of the cursor to another stored procedure in a parameter I shudder. If you did it in a one time procedure it would be a kludge at best. If I cam across that approach in a code review for production it would reflect poorly on you.

Best wishes,

Barry O'Connell

Chiranjib Chowdhury Old Hand Points: 326 More actions · Answer 7

Hi All,

First of all thanks 4 ur suggestions. One thing i want to tell u all that i am not a person who is very passionate about cursors. All my queries was simply due to curisity.

One more thing if u people can enlight me about the internals of cursors, i,e, how SQL Engine tackles cursors internally then it would be great 4 me. And again i am telling u i am not at all interested to use cursors in real life projects.

Thanks again

BarryOC SSC Veteran Points: 299 More actions · Answer 8

Hello,

The basis of Relational database theory is founded on two mathematical principles, Set Theory and Predicate Logic. When we make a SQL statement such as "Select * From BushEnemiesList" we are defining a set which may be 0 to very many rows. We can act on all those rows in one shot. When we open a cursor we deal with our data one row at a time. Compare it to buying groceries. With a set you would go to the market buy all the groceries and take them home. With a cursor you would make one trip to the market for each item. Ten cans of peas would take 10 trips to the market. Obviously no one would do that unless they had a crush on the cashier.

Best wishes,

Barry O'Connell

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 9

I didn't say passing the results of a cursor to another stored procedure was a GOOD idea

BarryOC SSC Veteran Points: 299 More actions · Answer 10

My apologies cliffb,

I did not mean to suggest that you did. It was just the thought of it I was responding to.

Best wishes,

Barry O'Connell