tenta algo assim:

 

CREATE PROCEDURE SampleProcedure @ParametroEntrada INT

AS

DECLARE @ErrorSave INT
SET @ErrorSave = 0

INSERT INTO Tabela
  (
   Campo1,
   Campo2
  )
 Values
  (
   Valor1,
   Valor2
  )

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

RETURN @ErrorSave
GO

 

 

 

 

exemplo da ajuda do SQL Server:

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 MinName, MaxName, 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 Max error.
RETURN @ErrorSave
GO

A Transact-SQL batch or stored procedure that executes a stored procedure can retrieve the return code into an integer variable:

DECLARE @ReturnStatus INT
DECLARE @MaxQtyVariable INT
EXECUTE @ReturnStatus = SampleProcedure @EmployeeIDParm = 9,
         @MaxQtyVariable = @MaxQuantity OUTPUT

-- Show the values returned.
PRINT ' '
PRINT 'Return code = ' + CAST(@ReturnStatus AS CHAR(10))
PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10))
GO