ตอนที่ 7 : การใช้ Cursor บน Stored Procedure (SQL Server : Stored Procedure)

-- Declare cursor 
DECLARE cursor_name CURSOR FOR 
SELECT ... FROM table_name

-- Open cursor
OPEN cursor_name 
FETCH NEXT FROM cursor_name 
INTO ...

-- Loop From Cursor
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
	

	FETCH NEXT FROM cursor_name -- Fetch next cursor
	INTO ....
END

-- Close cursor
CLOSE cursor_name ; 
DEALLOCATE cursor_name ; 

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 12-Sep-15 12:39:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]
	@pCountryCode	VARCHAR(2)	
AS
BEGIN
	
	-- Declare Variable
	DECLARE @sName VARCHAR(50)
	DECLARE @sEmail VARCHAR(50)

	-- Declare cursor from select table 'CUSTOEMR'
	DECLARE cursor_customer CURSOR FOR 
	SELECT NAME,EMAIL FROM CUSTOMER
	WHERE COUNTRY_CODE = @pCountryCode

	-- Open Cursor
	OPEN cursor_customer 
	FETCH NEXT FROM cursor_customer 
	INTO @sName, @sEmail;

	-- Loop From Cursor
	WHILE (@@FETCH_STATUS = 0) 
	BEGIN 
		
		-- Display
		PRINT 'Name : ' + @sName + ', Email : ' + @sEmail

		FETCH NEXT FROM cursor_customer -- Fetch next cursor
		INTO @sName, @sEmail  -- Next into variable
	END
	
	-- Close cursor
	CLOSE cursor_customer; 
	DEALLOCATE cursor_customer; 

END

EXEC myStoredProcedure 'US'