ตอนที่ 12 : การใช้ Cursor ระหว่าง (Between) Stored Procedure (SQL Server : Stored Procedure)

CREATE PROCEDURE [dbo].[procedure1]
	@pParameter1	VARCHAR(2),
	@pParameter2	CURSOR VARYING OUTPUT
AS
BEGIN

	-- Return cursor
    SET @pParameter2 = CURSOR FORWARD_ONLY STATIC FOR
		SELECT * FROM TABLE_NAME;

    OPEN @pParameter2;

END

CREATE PROCEDURE [dbo].[procedure2]
AS
BEGIN
		-- Declare Variable
		DECLARE @Col1 VARCHAR(4);
		DECLARE @Col2 VARCHAR(50);

		-- Declare Parameter
		DECLARE	@pParameter1 VARCHAR(2);
		DECLARE @pParameter2 CURSOR; -- for OUTPUT

		EXEC [procedure1] @pParameter1,@pParameter2 OUTPUT	
			
		FETCH NEXT FROM @pParameter2
		INTO @Col1, @Col2
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			PRINT @Col1 + ' ' + @Col2
			FETCH NEXT FROM @pParameter2 INTO @Col1, @Col2
		END
END

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[getByCountry]    Script Date: 11/27/2015 11:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getByCountry]
	@pCountryCode	VARCHAR(2),
	@pCursorCustomer	CURSOR VARYING OUTPUT
AS
BEGIN

	-- Return cursor
    SET @pCursorCustomer = CURSOR FORWARD_ONLY STATIC FOR
		SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode;

    OPEN @pCursorCustomer;

END

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[getCustomer]    Script Date: 11/27/2015 11:07:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getCustomer]
AS
BEGIN
		-- Declare Variable
		DECLARE @sCustomerID VARCHAR(4);
		DECLARE @sName VARCHAR(50);
		DECLARE @sEmail VARCHAR(50);
		DECLARE @sCountryCode VARCHAR(2);
		DECLARE @dBudget DECIMAL(18,2);
		DECLARE @dUsed DECIMAL(18,2);

		-- Declare Parameter
		DECLARE	@pCountryCode VARCHAR(2);
		SET @pCountryCode = 'US';
		DECLARE @pCursorCustomer CURSOR; -- for OUTPUT

		EXEC [getByCountry] @pCountryCode,@pCursorCustomer OUTPUT	
			
		FETCH NEXT FROM @pCursorCustomer
		INTO @sCustomerID, @sName, @sEmail, @sCountryCode, @dBudget,@dUsed
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			PRINT @sCustomerID + ' ' + @sName + ' ' + @sEmail
			FETCH NEXT FROM @pCursorCustomer INTO @sCustomerID, @sName, @sEmail, @sCountryCode, @dBudget,@dUsed
		END

END

EXEC [getCustomer]