RAISERROR is a SQL Server error-raising mechanism for returning a meaningful, application-specific error instead of leaving callers with only a low-level database failure. It is typically used in a CATCH block after error-handling work such as rolling back a transaction or recording diagnostic details. section 7.1
Mistake #26: letting errors escape without context. A safer pattern is to:
- Trap failures with TRY..CATCH, where execution moves from the failing TRY statement into the CATCH block. section 7.1
- Inspect ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE(), ERROR_PROCEDURE(), and ERROR_LINE() to identify the failure or log it. section 7.1
- Branch on an expected error when appropriate, then raise a message that describes the business problem clearly. section 7.1
- Treat severity deliberately: SQL Server severity levels run from 0 through 24, from informational messages to critical failures. section 7.7
Duplicate sales order
A sales-order procedure can detect a duplicate-key failure and return “A duplicate sales order has been entered.” rather than exposing only the primary-key constraint message. The custom message is registered as error 50001 with severity 16, then raised with RAISERROR(). This gives a caller a stable, understandable failure while preserving a path for logging and support. section 7.1
RAISERROR() and THROW both raise meaningful errors. Use either as part of complete error handling, not as a substitute for it: failed transactional work still needs appropriate rollback behavior, and unattended applications may need alerts through an observability tool or SQL Server alerting facilities. section 7.1 section 7.7
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 revised InsertSalesOrder procedure to see RAISERROR() placed in a transactional TRY..CATCH workflow for a duplicate-order condition. section 7.1
