declare cursor for a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

declare cursor for a stored procedure

Author

Topic

cez
Starting Member

37 Posts

Posted - 2002-11-05 : 18:04:56

Hi !

How can I declare a cursor on a stored procedure results ?
Something like :
declare myCursor cursor for
exec my_stored_procedure @parameter1, @parameter2

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 18:13:07

Why do you want to use a cursor?

cez
Starting Member

37 Posts

Posted - 2002-11-05 : 18:20:04

quote:

Why do you want to use a cursor?

Because I have some operations that I need to perform on the results of the stored procedure.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 18:32:58

Why do you NEED to use a cursor?

cez
Starting Member

37 Posts

Posted - 2002-11-05 : 18:39:11

AM I SPEAKING ENGLISH ? Why I need a cursor is only my problem.

If you can help me please respond to my post, in the subject. If not, just read and go on.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-05 : 19:16:58

OK
The answer to this is, you need to put the results of the stored proc into a temp table. Then open your cursor on that.

I.e.

Create Table #temp(
val1 int,
val2 char(10)
)

Insert into #Temp
Exec MyStoredsProc @Parameter1=1, @Parameter2 = 'aaaa'

Now, in future, if you want answers to questions BE POLITE.

You would be amazed at the number of questions that have been posted on this forum about cursors that have been solved using a MUCH better and quicker solution.

You are asking for free support, the least you can do is define the problem if someone asks.

Damian

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-06 : 02:08:28

What Rob was trying to tell you in a polite way was, that you should awoid using cursors at ANY costs. He implied that, if you need cursors on a resultset you should worry about your apps logic (or design)...

cez
Starting Member

37 Posts

Posted - 2002-11-06 : 03:27:36

quote:

What Rob was trying to tell you in a polite way was, that you should awoid using cursors at ANY costs. He implied that, if you need cursors on a resultset you should worry about your apps logic (or design)...

It's a false idea that if you declare a cursor, you should worry about your apps logic. In all your projects you are using only selects ?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-06 : 03:40:04

Since I joined this forum (and started reading Ken Henderson books

), I have learned that cursors are needed only in extremely rare cases. Most "new" SQL programmers who are used to programming conventional languages (VB, C and so on) tend to use cursors a lot. I guess it's because cursors behave like loops (for, while etc).

Anyways, in some cases cursors are needed, but performance-wise you should always try to reduce your use of cursors to a minimum.

cez
Starting Member

37 Posts

Posted - 2002-11-06 : 04:51:14

Ok, let's see how you can do this without having a cursor:

I have a table for properties:
prp_name, prp_type, prp_val1, prp_val2, prp_val3, ..prp_val10

The columns prp_val1..10 are filled depending on the property type.
I declare a cursor for creating a #temp table having the columns' names the name of the properties, and having the rows the property value depending on type.

For example, let's suppose I have the properties table like following:
Property1, 1, 'some text', null, null, ... null
Property2, 2, null, -10, null, null, ..
Property3, 3, null, 5, 7, null, null, ..

My #temp table will look like:
Property1 Property2 Property3 ..
some text -10 5-7

Do you think it's possible to avoid cursors ?

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-06 : 04:59:35

quote:

Do you think it's possible to avoid cursors ?

Yes.

I also think the table is suffering a little from lack of normalisation.

cez
Starting Member

37 Posts

Posted - 2002-11-06 : 05:03:18

quote:

I also think the table is suffering a little from lack of normalisation.

How so ?

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-06 : 05:07:11

quote:

quote:

I also think the table is suffering a little from lack of normalisation.

How so ?

I would have the repeating property values in a separate table.

cez
Starting Member

37 Posts

Posted - 2002-11-06 : 05:11:59

quote:

I would have the repeating property values in a separate table.

Oh, I do, but this is just a *simple* (hope clear) example of my situation.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-06 : 05:21:28

quote:

quote:

I would have the repeating property values in a separate table.

Oh, I do, but this is just a *simple* (hope clear) example of my situation.

I fear that I must be being a bit simple, because it's not clear to me what you're trying to achieve.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-06 : 05:36:40

That looks like a cross tab with a coalesce and a bit more.
Should be able to do it with dynamic sql - think Rob wrote an article on cross tab and the extra sholdn't be too hard.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-06 : 06:28:44

IRONY often doesn't travel or translate well.....and if an expert such as Rob asks the question, he's not being smart....he's challenging you to confront your own starting point...."that the solution needs a cursor"

have a look at the two following topics....especially the latter one.....on cursor performance....you may change your mind about needing them.....(a forum search on "cursor + performance" will turn up loads of other conversations, which may convince you that cursors should be your last choice when it comes to working with SQL.)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7149
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9126

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-11-08 : 12:26:20

I actually found one case where a cursor was preferable to set processing. This was in a trigger. I did so much testing that my head hurt but I could not escape the conclusion that the cursor was faster in this one instance.

I still work with one "programmer" who insists that cursors are not inefficient even though I completely rewrote his 8 hour running app so that it ran in under two.

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 22:00:05

yes really

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

but you can see the article in
http://www.sqlteam.com/item.asp?ItemID=3856
that how can a cursor can get better perfomance in some rare ocassions.

again

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thanks nr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources