CODEWITHSUNDEEP
sql-serverdatabasestored-proceduresdatabase-cursor
Deepak Kavin
Deepak Kavin

Reputation: 39

SQL Server 2014: create procedure with cursor output parameter

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:

  1. Create procedure basic template
  2. Create procedure with cursor output parameter
  3. 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;"

Upvotes: 0

Views: 6869

Answers (2)

EzLo
EzLo

Reputation: 14189

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.

Upvotes: 0

Eralper
Eralper

Reputation: 6612

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

Upvotes: 1

Related Questions