ตอนที่ 10 : การใช้ OUTPUT และ OUT เพื่อส่งค่ากลับ (SQL Server : Stored Procedure)

@CountCustomer INT OUTPUT -- return this variable

@CountCustomer INT OUT-- return this variable

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].[getCustomerCount]
	@pCountryCode	VARCHAR(2),
	@CountCustomer INT OUTPUT -- return this variable
AS
BEGIN

	-- set count customer
	SELECT @CountCustomer = COUNT(*) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

END

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 @iRowCustomer INT 

	-- call stored procedure getCustomerCount
	EXEC getCustomerCount @pCountryCode,@iRowCustomer OUTPUT

	SELECT 'Total customer : ' + CAST(@iRowCustomer AS VARCHAR(10))

END

EXEC myStoredProcedure 'US'

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[getCustomer]    Script Date: 10/23/2015 22:22:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getCustomer]
	@pCountryCode	VARCHAR(2),
	@pRowFound	INT OUTPUT
AS
BEGIN

	-- set rowcount
	SELECT @pRowFound = COUNT(*) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

	-- set customer
	SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

END

DECLARE	@pRowFound int
EXEC	getCustomer @pCountryCode = 'US',@pRowFound = @pRowFound OUTPUT
SELECT	@pRowFound AS 'pRowFound';

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[insertCustomer]    Script Date: 10/23/2015 22:41:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertCustomer]
	@pCustomerID	VARCHAR(4),
	@pName			VARCHAR(50),
	@pEmail			VARCHAR(50),
	@pCountryCode	VARCHAR(2),
	@pBudget		DECIMAL(18,2),
	@pUsed			DECIMAL(18,2),
	@pResult		INT OUTPUT,
	@pMessage		VARCHAR(500) OUTPUT
AS
BEGIN

	BEGIN TRY
		-- insert customer
		INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
		SET @pResult = @@ROWCOUNT;
		SET @pMessage = 'Insert Data Successfully';
	END TRY
	BEGIN CATCH
		SET @pResult = 0;
		SELECT @pMessage = ERROR_MESSAGE();
	END CATCH

END

DECLARE	@pResult INT,
		@pMessage VARCHAR(500)

EXEC [insertCustomer] 'C005','Fun Wipa','[email protected]','TH','100000','0',@pResult=@pResult OUTPUT,@pMessage=@pMessage OUTPUT

SELECT	@pResult AS 'pResult'
SELECT	@pMessage AS 'pMessage'