Create procedure - TSQL Tutorial

In this page you can learn how to create and execute an procedure and use it with the cursors.

Create procedure example:

USE model;
GO
IF OBJECT_ID ( 'dbo.productProc', 'P' ) IS NOT NULL
   DROP PROCEDURE dbo.productProc;
GO
CREATE PROCEDURE dbo.productProc
   @varCursor CURSOR VARYING OUTPUT
AS
   SET NOCOUNT ON;
   SET @varCursor = CURSOR
   FORWARD_ONLY STATIC FOR
     SELECT product_id, product_name
     FROM dbo.products;
   OPEN @varCursor;
GO

Output
Command(s) completed successfully.

Execute procedure example:

USE model;
GO
DECLARE @Cursor_name CURSOR;
EXEC dbo.productProc @varCursor = @Cursor_name OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
   FETCH NEXT FROM @Cursor_name;
END;
CLOSE @Cursor_name;
DEALLOCATE @Cursor_name;
GO

product_idproduct_name
1pro_1
2pro_2
3pro_3
4pro_4