A stored procedure is a named database routine that accepts parameters and performs database work. Treat it as an operational boundary: a failure inside a multi-step write can leave an open transaction and confuse both callers and support staff. section 7.1
Avoid the “it failed, so it must have rolled back” mistake:
- Check @@TRANCOUNT after a failed execution; an unhandled error can leave the count at 1, requiring ROLLBACK. section 7.1
- Handle expected failures with TRY..CATCH, and make duplicate-order failures identifiable with a custom error message. section 7.1
- Log useful error details so application support can investigate production failures. section 7.1
A sales-order failure
dbo.InsertSalesOrder builds a customer prefix and inserts sales-order header and detail records inside a transaction. Renaming SalesOrderDetails makes the insert fail; without error handling, the transaction remains open rather than rolling back automatically. section 7.1
Listing 7.1 Creating the dbo.InsertSalesOrder stored procedure
CREATE PROCEDURE dbo.InsertSalesOrder
@SalesOrderNumber NVARCHAR(12),
@SalesOrderDate DATE,
@SalesPersonID INT,
@SalesAreaID INT,
@CustomerID INT,
@SalesOrderDeliveryDueDate DATE,
@SalesOrderDeliveryActualDate DATE,
@CurrierUsedForDelivery NVARCHAR(32),
@ProductID INT,
@Quantity INT
AS
BEGIN
BEGIN TRANSACTION
DECLARE @CustomerPrefix NVARCHAR(12) ;
SET @CustomerPrefix = (
SELECT SUBSTRING(CustomerCompanyName,1,3)
FROM dbo.Customers
WHERE CustomerID = @CustomerID
) ;
INSERT INTO dbo.SalesOrderHeaders
VALUES (
@CustomerPrefix + @SalesOrderNumber
, @SalesOrderDate
, @SalesPersonID
, @SalesAreaID
, @CustomerID
, @SalesOrderDeliveryDueDate
, @SalesOrderDeliveryActualDate
, @CurrierUsedForDelivery
) ;
INSERT INTO dbo.SalesOrderDetails (
ProductID
, Quantity
, SalesOrderNumber
)
VALUES (
@ProductID
, @Quantity
, @CustomerPrefix + @SalesOrderNumber
) ;
COMMIT
END
Inspect the parameterized InsertSalesOrder routine and its transaction-wrapped header and detail inserts. section 7.1
