SQL Server 2014: create procedure with cursor output parameter

Question

I am new to this stored procedures my question is related to cursor output parameter. what is the difference between cursor output parameter and a normal stored procedure like it just a variable or it effect the result or performance of query?

I am using SQL Server 2014. Creating a stored procedure I used the shortcut key alt+k, alt+x. in the list I have selected stored procedure after selecting the stored procedure, it ask to choose stored procedure type:

Create procedure basic template
Create procedure with cursor output parameter
Create procedure with output parameter.

I couldn't understand the 2nd stored procedure type. I tried to google but didn't get sufficient information. Anyone here to help me to understand will much appreciated. I have attached the 2nd stored procedure type sample script

CREATE PROCEDURE dbo.Sample_Procedure 
    @sample_procedure_cursor CURSOR VARYING OUTPUT
AS
    SET @sample_procedure_cursor = CURSOR FOR
        select 1
    OPEN @sample_procedure_cursor 
RETURN 0

I just want to understand is there any other output I can't see using "cursor varying output" keywords instead of using "@variable datatype;"

Why do you need this? I did not heard of cursor output parameters. But you can create a procedure that has a cursor inside... — Eralper
– Eralper, Commented Feb 8, 2019 at 10:14
Use ALT+S, And ALT+X in sql server query window then select Stored procedure among the list. then you will see these three type. I am trying to understand clear my SP Concept. Till before I see this I felt like I Getting know SP very well but this make me curious. — Deepak Kavin
– Deepak Kavin, Commented Feb 8, 2019 at 10:19
I did not know that. Thanks I leant a new thing. Let me check if I can understand documentation — Eralper
– Eralper, Commented Feb 8, 2019 at 10:21
Why are you trying to return a cursor at all? You don't need to return a cursor to return results. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 8, 2019 at 10:27
@DeepakKavin, although this may be a good learning exercise, be aware that cursor parameters are rarely used in SQL Server in practice. I often see those in T-SQL code migrated from other DBMS products which require cursors to retrieve results. SQL Server natively streams rows to the client without the need for a server-side cursor. It's generally best to use set-based processing and avoid cursors. — Dan Guzman
– Dan Guzman, Commented Feb 8, 2019 at 10:49

Eralper · Accepted Answer · 2019-02-08 10:29:42Z

1

Deepak please refer to documentation samples at reference

If you are using the same cursor repeatedly in your SQL codes you can wrap the definition of the cursor into a SP once and refer to it later.

I copy below the sample code

First create the procedure

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO

Then use it as follows

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO

answered Feb 8, 2019 at 10:29

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EzLo · Accepted Answer · 2019-02-08 10:54:04Z

A cursor as output is meant to encapsulate the definition of the cursor. This means that you execute an SP to retrieve a cursor that has already been initialized and is linked to a result set which is unknown to the caller, but the caller will use.

This opens up a potential problem in that the caller will need to know which variables to cast the fetching row into and might potentially break the usage of the cursor if done incorrectly. This wouldn't happen if the SP returned a result set with a SELECT or inserts into a temporary table created outside, for example.

In my opinion, there is little to none useful applications of this. To start with it's on very rare occasions that you want to use a cursor at all and they are usually with operations that don't involve DML and involve system operations, like creating files or sending emails. And even in those cases, hiding the result set from the caller seems pretty obscure.

Collectives™ on Stack Overflow

SQL Server 2014: create procedure with cursor output parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related