SQL Server: Sử dụng Stored Procedure với Return, GoTo

CREATE PROCEDURE CustomerDetails.apf_CustBalance
@CustomerId int,
@ClearedBalance money OUTPUT, @UnclearedBalance money OUTPUT
AS

BEGIN

SELECT @ClearedBalance = ClearedBalance, @UnclearedBalance = UnclearedBalance
FROM CustomerDetails.Customers WHERE CustomerId = @CustomerId
RETURN @@ROWCOUNT

END
GO

--Sau đây là cách gọi nó

DECLARE @ClearedBalance Money, @UnclearedBalance Money
DECLARE @RetVal int
EXECUTE @RetVal=CustomerDetails.apf_CustBalance 2,
@ClearedBalance OUTPUT,
@UnclearedBalance OUTPUT
SELECT @RetVal AS ReturnValue, @ClearedBalance AS ClearedBalance,
@UnclearedBalance AS UnclearedBalance
GO

Tiếp theo là một ví dụ về sử dụng GOTO trong SQL Server Stored Procedure

DECLARE @Name nvarchar(50) = 'Engineering';
DECLARE @GroupName nvarchar(50) = 'Research and Development';
DECLARE @Exists bit = 0;
IF EXISTS (
SELECT Name
FROM HumanResources.Department
WHERE Name = @Name)
BEGIN
SET @Exists = 1;
GOTO SkipInsert;
END;
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES(@Name , @GroupName);
SkipInsert: IF @Exists = 1
BEGIN
PRINT @Name + ' already exists in HumanResources.Department';
END