The Example

Let’s now look at the debit/credit example we documented in the stored procedure deployment plan discussed earlier.

 CREATE PROC /* Script Name: jrs_CRDR Description: Credit/Debit for Items/Orders Usage: For stock picking Return Code: -1 to -10 Author: Jeffrey R. Shapiro Version: 1.1 Date Created: 9/25/2005 Revision History: */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO /****** object: Stored Procedure dbo.jrs_CRDR Script Date: 9/25/2005 11:34:35 PM ******/ ALTER PROCEDURE dbo.jrs_CRDR @Credit int=0, --Amount to credit to order (3) @SKU int=0,    --This is the SKU in the Items table (ItemNumber) @IN Int=0      --This is the Order's ItemNumber (OrderItem) AS BEGIN TRANSACTION   DECLARE @Amt int, @Debit int        --Value for current number of   Items in stock   SET @Amt = 0 /*assignment not necessary but nice to see how it changes   in the debugger*/ --First get the stock level for the sku and see if we can debit SELECT @Amt = (SELECT Quantity FROM Customers.dbo.Items     WHERE ItemNumber = @SKU)   IF @Amt IS NULL   BEGIN    ROLLBACK TRANSACTION    RAISERROR       ('Bad SKU. Please call stock controller about %d', 16, 1, @SKU)    RETURN (-4)   END   IF @Amt < @Credit   BEGIN     ROLLBACK TRANSACTION     RAISERROR       ('Low stock level, %d. Please call stock controller', 16, 1, @Amt)     RETURN (-5)     END  --Get values for debit/credit SELECT @Debit = (@Amt - @Credit) --Next debit from stock (trigger on Items stock levels) UPDATE Customers.dbo.Items Set Quantity = (@Debit)   WHERE ItemNumber = @SKU                         --at this sku  --check if debit items failed   IF @@ROWCOUNT = 0   BEGIN    ROLLBACK TRANSACTION    RETURN (-6)   END --Now credit customer order     UPDATE Customers.dbo.ORDERS Set CanShip = (@Credit)           --credit     orders       WHERE OrderItem = @IN                    --at this item number --check if credit customer order failed   IF @@ROWCOUNT = 0   BEGIN    ROLLBACK TRANSACTION    RETURN (-7)   END --check if any errors from   IF @@ERROR <> 0   BEGIN    ROLLBACK TRANSACTION    RETURN (-8)   END COMMIT   PRINT 'Item posted' GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

In the preceding code I used PRINT to report that the item posted okay (thanks to the commit that only gets called if the transaction is kosher). But I coded in various return codes that would get returned on an error. These could be suppressed and confined to the stored procedure or returned as an output value to the client. In other words you can loop through return codes in the procedure and send a related message to the user, or just send the return code to the client and let logic on the client decide how to proceed. I prefer to keep the error codes local to the procedure, which means I can change, at any time, what I tell the client about the errors or what course of action to take.