ตอนที่ 6 : การสร้าง Loop ข้อมูลบน Stored Procedure (SQL Server : Stored Procedure)

WHILE (Condition)
  BEGIN 
    //Statement
  END -- WHILE

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 11-Sep-15 3:29:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]
	@pLimitRow	INT	
AS
BEGIN
	
		DECLARE @i INT
		SET @i = 1

		WHILE (@i <= @pLimitRow)
		  BEGIN 

			PRINT + ' Row : ' + CONVERT(VARCHAR,@i)
         
			SET @i = @i + 1 
		  END -- WHILE

END

GO

EXEC myStoredProcedure '10'

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 11-Sep-15 3:29:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]

AS
BEGIN
	
	-- Declare Table
	DECLARE @temp TABLE
	(
		RowID INT IDENTITY (1,1),
		Name VARCHAR(50),
		LastName VARCHAR(50)
	)

	-- Insert Table
	INSERT INTO @temp (Name,LastName) VALUES ('Weerachai','Nukitram')
	INSERT INTO @temp (Name,LastName) VALUES ('Surachai','Sirisart')
	INSERT INTO @temp (Name,LastName) VALUES ('Adisorn','Boonsong')

	-- Variable
	DECLARE @i INT
	DECLARE @iRow INT
	DECLARE @sName VARCHAR(50)
	DECLARE @sLastName VARCHAR(50)

	-- Set Variable
	SET @i = 1
	SELECT @iRow = COUNT(*) FROM @temp

	-- Loop Row
	WHILE (@i <= @iRow)
		BEGIN 
			-- Select from table
			SELECT @sName = Name , @sLastName = LastName
			FROM @temp WHERE RowID = @i

			-- Print output
			PRINT ' Row : ' + CONVERT(VARCHAR,@i) + ', Name : ' + @sName + ', LastName : ' + @sLastName
         
		SET @i = @i + 1 
		END -- WHILE

END

GO

EXEC myStoredProcedure