ตอนที่ 4 : การสร้างตัวแปร Declare Variable บน Stored Procedure (SQL Server : Stored Procedure)

DECLARE  @sName VARCHAR(50) -- แบบ Varchar หรือ String
DECLARE  @iCount INT -- แบบ Integer สามารถใช้ได้แบบ INT หรือ INTEGER
DECLARE  @iTotalBudget DECIMAL(20,2) -- แบบ Decimal

SET @sName = "Weerachai Nukitram"

SELECT @iCount = COUNT(*) FROM table_name

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]
	@pCountryCode	VARCHAR(2),
	@pUsed			INTEGER			
AS
BEGIN
	
	-- Declare Variable
	DECLARE  @iTotalBudget DECIMAL(20,2)
	DECLARE  @iTotalUsed DECIMAL(20,2)

	-- Sum Budget
	SELECT @iTotalBudget = SUM(BUDGET) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
		AND USED >= @pUsed

	-- Sum Used
	SELECT @iTotalUsed = SUM(USED) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
		AND USED >= @pUsed

	PRINT 'pCountryCode : ' + @pCountryCode
	PRINT 'pUsed : ' + CAST(@pUsed AS VARCHAR(10))
	PRINT 'Total Budget : ' + CAST(@iTotalBudget AS VARCHAR(10))
	PRINT 'Total Used : ' + CAST(@iTotalUsed AS VARCHAR(10))

END

GO

EXEC myStoredProcedure 'US','100000'

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]
	@pCountryCode	VARCHAR(2),
	@pUsed			INTEGER			
AS
BEGIN
	
	-- Declare Variable
	DECLARE  @iQueryRowCount INT
	DECLARE  @iTotalBudget DECIMAL(20,2)
	DECLARE  @iTotalUsed DECIMAL(20,2)
	DECLARE  @iDiffBudget DECIMAL(20,2)

	-- Sum Budget & Sum Used
	SELECT @iTotalBudget = SUM(BUDGET) , @iTotalUsed = SUM(USED) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
		AND USED >= @pUsed

	-- Diff Budget
	SET @iDiffBudget = @iTotalBudget - @iTotalUsed

	-- Row Count
	SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode
		AND USED >= @pUsed
	SET @iQueryRowCount = @@ROWCOUNT

	PRINT 'pCountryCode : ' + @pCountryCode
	PRINT 'pUsed : ' + CAST(@pUsed AS VARCHAR(10))
	PRINT 'Total Budget : ' + CAST(@iTotalBudget AS VARCHAR(10))
	PRINT 'Total Used : ' + CAST(@iTotalUsed AS VARCHAR(10))
	PRINT 'Diff Budget : ' + CAST(@iDiffBudget AS VARCHAR(10)) 
	PRINT 'Row Count : ' + CAST(@iQueryRowCount AS VARCHAR(10))

END

GO

EXEC myStoredProcedure 'US','100000'