CURSOR as OUTPUT of the Stored Procedure.

Question

CURSOR as OUTPUT of the Stored Procedure.

SQL*

SSCertifiable

Points: 6638
More actions
July 30, 2012 at 3:25 am

#259511

Hi All,
I have read that CURSOR Type can be used as OUTPUT variable in the stored procedure.
Can any one explain in which scenario we should go for CURSOR data type as OUTPUT in the Stored Procedure.
Thanks,.
🙂

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

SQL* (7/30/2012)
Hi All,
I have read that CURSOR Type can be used as OUTPUT variable in the stored procedure.
Can any one explain in which scenario we should go for CURSOR data type as OUTPUT in the Stored Procedure.
Thanks,.

Only when you're using Oracle. 😉

--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)

SQL* SSCertifiable Points: 6638 More actions · Answer 2

That means only we have to use in Oralce not in SQL Server?

if yes, why we have this option in sql server?

🙂

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

SQL* (7/30/2012)
That means only we have to use in Oralce not in SQL Server?
if yes, why we have this option in sql server?

You can't output a cursor in SQL Server. You don't have that option.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

Grant Fritchey (7/30/2012)
You can't output a cursor in SQL Server. You don't have that option.

I think you can (not that I'm advocating it)...

USE tempdb

GO

CREATE PROCEDURE obj_cursor @obj_cursor CURSOR VARYING OUTPUT

AS

SET @obj_cursor = CURSOR

FORWARD_ONLY STATIC FOR

SELECT top 10 name from sys.objects

OPEN @obj_cursor

GO

DECLARE @cur CURSOR

DECLARE @name sysname

EXEC obj_cursor @obj_cursor = @cur OUTPUT

FETCH NEXT FROM @cur into @name

WHILE (@@FETCH_STATUS = 0)

BEGIN

print @name

FETCH NEXT FROM @cur into @name

END

CLOSE @cur

DEALLOCATE @cur

GO

DROP PROC obj_cursor

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

Ian Scarlett (7/30/2012)
Grant Fritchey (7/30/2012)
You can't output a cursor in SQL Server. You don't have that option.
I think you can (not that I'm advocating it)...
USE tempdb
GO
CREATE PROCEDURE obj_cursor @obj_cursor CURSOR VARYING OUTPUT
AS
SET @obj_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT top 10 name from sys.objects
OPEN @obj_cursor
GO
DECLARE @cur CURSOR
DECLARE @name sysname
EXEC obj_cursor @obj_cursor = @cur OUTPUT
FETCH NEXT FROM @cur into @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
print @name
FETCH NEXT FROM @cur into @name
END
CLOSE @cur
DEALLOCATE @cur
GO
DROP PROC obj_cursor

Cool! I'm wrong.

What the heck do you do with it?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 6

Grant Fritchey (7/30/2012)
What the heck do you do with it?

Beats the heck out of me!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Ian Scarlett (7/30/2012)
Grant Fritchey (7/30/2012)
What the heck do you do with it?
Beats the heck out of me!

Encapsulate the definition of a cursor. Why you'd want to do that though is another question.

Not even a perfect encapsulation, since you need to know what columns it returns to use it.

Probably one of those ANSI standard things that has to be implemented even though it's not really useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Could be to allow code written to use reference cursors from Oracle storded procedures to use cursors returned by SQL Server.

Reference cursors are the only way to return result sets to a calling program (other stored proc or external program) in Oracle.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Grant Fritchey (7/30/2012)
Ian Scarlett (7/30/2012)
Grant Fritchey (7/30/2012)
You can't output a cursor in SQL Server. You don't have that option.
I think you can (not that I'm advocating it)...
USE tempdb
GO
CREATE PROCEDURE obj_cursor @obj_cursor CURSOR VARYING OUTPUT
AS
SET @obj_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT top 10 name from sys.objects
OPEN @obj_cursor
GO
DECLARE @cur CURSOR
DECLARE @name sysname
EXEC obj_cursor @obj_cursor = @cur OUTPUT
FETCH NEXT FROM @cur into @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
print @name
FETCH NEXT FROM @cur into @name
END
CLOSE @cur
DEALLOCATE @cur
GO
DROP PROC obj_cursor
Cool! I'm wrong.
What the heck do you do with it?

Hmm - unless I'm reading this wrong - the cursor isn't the OUTPUT of the procedure, it's BASED on the output of the stored proc. Still not the same as the cursor output form Oracle (basically just a formalized version of the recordsets we get out of stored procs).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?