How to use stored procedure instead of cursors?

Question

Post reply

How to use stored procedure instead of cursors?

vijayakumar.rama

Valued Member

Points: 67
More actions
August 5, 2008 at 6:16 am

#93694

Hi i am new to SQL SERVER,
please any one help me.
thanks
vijay

Viewing 15 posts - 1 through 14 (of 14 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

August 5, 2008 at 6:20 am

#853130

i sure some one can, what is your question?

vijayakumar.rama Valued Member Points: 67 More actions · Answer 2

Hi

I have used oracle before,

problem is most of the stored procedure in our application based on cursor used for returning table records

how it is achived in sql server using stored procedure

thanks

vijay

Talib Ali Khan Old Hand Points: 301 More actions · Answer 3

Hi,

Can you explain more in detail , what you want to achieve ..... So that it'll be more easier to help you out.

Best Regards,
TALIB ALI KHAN
MCTS
http://www.onlineasp.net

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Oracle is a lot better suited to working with cursors, SQL server works best with sets of data.

It is a bit hard to help as it depends on what your procedure is doing and what you want the results to be.

Post the procedure with some table defs and sample data, and i am sure someone can help

Chris Harshman-840382 SSC Journeyman Points: 80 More actions · Answer 5

At the most basic level, there are 2 types of queries in a SQL Server stored procedure. There are ones that set the value of a variable such as:

SELECT @myvariable = column_name

FROM sometable

WHERE ...

Then there are ones that will produce a resultset back to the calling program. A single stored procedure can return multiple resultsets by having multiple select statements in it. So if you didn't have variable assignments like above, and instead had:

SELECT column_name1, column_name2

FROM sometable

WHERE ...

the stored procedure would send a resultset back to the program.

vijayakumar.rama Valued Member Points: 67 More actions · Answer 6

vijayakumar.rama

Valued Member

Points: 67

August 6, 2008 at 8:41 pm

#854194

Hi all

Thanks for reply

vijay

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

It's a paradigm shift... with Cursors, you think "rows"... with setbased programming, you must learn to think "columns"... as in "What do I need to do to this column for everything in the result set?"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

do u still need alternative of CURSORS ???

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Please don't suggest replacing a cursor with a Temp table and While loop... they're the same thing if you use a read only, forward only cursor. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

ok i wont ....then do you have any other alternative other than TEMP table for CURSOR:discuss:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Thousands... depends on the requirements of the problem. For example, most folks think you either need a Cursor or Temp Table to do running totals. Sure, there's the "other" way that uses a performance parallizing Triangular Join, but there's a better way...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

How about creating a temporary table of dates in a range or splitting a whole table's worth CSV's?

http://www.sqlservercentral.com/articles/TSQL/62867/

How about passing arrays?

http://www.sqlservercentral.com/articles/T-SQL/63003/

People just give up on set based because they think it's too difficult or will take too much time to develop. As a result, they end up making "time bombs" in their code. Read Only Forward Only cursors are just as effective as Temp Tables and While loops and both should be avoided because both are painfully slow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 12

No offense Jeff, but I think you're taking the discussion a bit off topic. The original question had nothing to do with cursors as you know them in SQL Server, but of the REF_CURSOR datatype from Oracle that the original poster had used before as output parameters in stored procedures. In Oracle, stored procedures don't automatically return a result set from a SELECT statament. In SQL Server, a SELECT statement either:

a.) assigns values of columns from a singleton select to a variable or variables

b.) populates another table with SELECT... INTO... or INSERT INTO... SELECT...

c.) returns a resultset to the calling program or procedure

That SQL Server makes it so easy to return a resultset from a stored procedure also makes it easier to code an application than dealing with REF_CURSORs in Oracle.

Masood Ali-854067 SSC Enthusiast Points: 190 More actions · Answer 13

i suggest using the update command as an alternative to cursors.

UPDATE T_TEMP SET

field1 = P.parent_ID FROM table P

JOIN T_TEMP t on p.id=t.id

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

charshman (8/19/2008)
No offense Jeff, but I think you're taking the discussion a bit off topic. The original question had nothing to do with cursors as you know them in SQL Server, but of the REF_CURSOR datatype from Oracle that the original poster had used before as output parameters in stored procedures. In Oracle, stored procedures don't automatically return a result set from a SELECT statament. In SQL Server, a SELECT statement either:
a.) assigns values of columns from a singleton select to a variable or variables
b.) populates another table with SELECT... INTO... or INSERT INTO... SELECT...
c.) returns a resultset to the calling program or procedure
That SQL Server makes it so easy to return a resultset from a stored procedure also makes it easier to code an application than dealing with REF_CURSORs in Oracle.

Heh... yeah... I agree... the use of Oracle's Ref_Cursor is most likely what the op was talking about. I'm afraid the word "cursor" has become like a red cape in front of a bull. Thanks for the pullback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)