--sample table and data
create table TestDel
(loginId	int identity(1,1),
val		int)
go
insert into testdel (val)
select 1 union all select 2 union all select 3

select * from testdel

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_testdel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_testdel]
GO
 
SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS OFF 
SET NOCOUNT ON
GO

CREATE PROCEDURE up_testdel
(@LoginID int)
 AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

DECLARE @Error		INTEGER	--capture @@error
DECLARE @RowCount	INTEGER	--capture @@rowcount
--DECLARE @ReturnResult	INTEGER	--capture return value to hand back

--If we can use RaiseError:
-- DECLARE @DatabaseId INT
-- SET @DatabaseId = DB_ID()
-- 
-- DECLARE @DatabaseName NVARCHAR(128)
-- SET @DatabaseName = DB_NAME()

--We COULD consider adding "design by contract" by checking for valid value and number of rows returned,
-- i.e. select @ContractCheckLoginId = count(*) from dbo.TestDel
--	if @ContractCheckLoginId <> 1 the fail
-- but we don't have defined way of enabling atm the moment.

BEGIN TRANSACTION --unless you handle this outside
DELETE  FROM dbo.TestDel WHERE Loginid = @LoginID
SELECT 	@RowCount=@@rowcount,@Error = @@error
IF @Error <> 0
	-- Can we handle RaiseError messages?
	BEGIN
	ROLLBACK TRANSACTION --remove if transaction handled outside. NB then to use RETURN value to ensure rollback
	-- RAISERROR ('Error deleting from table TestDel - rolled rack transaction',16,1,@DatabaseId,@DatabaseName)
	RETURN -1 --Use -1 to indicate serious error processing SQL
END
IF @RowCount <> 1
	BEGIN
	ROLLBACK TRANSACTION --remove if transaction handled outside. NB then to use RETURN value to ensure rollback
	-- RAISERROR ('Incorrect number of rows deleted - rolled rack transaction',16,1,@DatabaseId,@DatabaseName)
	RETURN -2 --Use -2 to indicate incorrect number of rows affected. Breach of contract effectively.
	END
	ELSE
		COMMIT TRANSACTION --remove if transaction handled outside. NB then to use RETURN value to ensure rollback
RETURN 0
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_testdel2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_testdel2]
GO
 
SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS OFF 
SET NOCOUNT ON
GO

CREATE PROCEDURE up_testdel2
(@LoginID int)
 AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

DECLARE @Error		INTEGER	--capture @@error
DECLARE @RowCount	INTEGER	--capture @@rowcount
DECLARE @ReturnResult	INTEGER	--capture return value to hand back

--If we can use RaiseError:
-- DECLARE @DatabaseId INT
-- SET @DatabaseId = DB_ID()
-- 
-- DECLARE @DatabaseName NVARCHAR(128)
-- SET @DatabaseName = DB_NAME()

--We COULD consider adding "design by contract" by checking for valid value and number of rows returned,
-- i.e. select @ContractCheckLoginId = count(*) from dbo.TestDel
--	if @ContractCheckLoginId <> 1 the fail
-- but we don't have defined way of enabling atm the moment.

BEGIN TRANSACTION --unless you handle this outside
DELETE  FROM dbo.TestDel WHERE Loginid = @LoginID
SELECT 	@RowCount=@@rowcount,@Error = @@error
IF (@Error = 0 and @RowCount = 1)
	Begin
	SET @ReturnResult = 0
	COMMIT TRANSACTION
	GOTO ReturnResult
END
IF @Error <> 0
	-- Can we handle RaiseError messages?
	BEGIN
	ROLLBACK TRANSACTION --remove if transaction handled outside. NB then to use RETURN value to ensure rollback
	-- RAISERROR ('Error deleting from table TestDel - rolled rack transaction',16,1,@DatabaseId,@DatabaseName)
	SET @ReturnResult = @Error--Use -1 to indicate serious error processing SQL
	GOTO ReturnResult
END
IF @RowCount <> 1
	BEGIN
	ROLLBACK TRANSACTION --remove if transaction handled outside. NB then to use RETURN value to ensure rollback
	-- RAISERROR ('Incorrect number of rows deleted - rolled rack transaction',16,1,@DatabaseId,@DatabaseName)
	SET @ReturnResult = -2 --Use -2 to indicate incorrect number of rows affected. Breach of contract effectively.
	GOTO ReturnResult
END

ReturnResult:
RETURN @ReturnResult
GO

USE Northwind
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
         @MaxQuantity INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0

-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO

CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @param) = 'CA'
   RETURN 1
ELSE
   RETURN 2