RAISERROR with Parameters in SQL Server

To raise meaningful, dynamic business errors in SQL Server using custom messages with severity and runtime values we are using RAISERROR with Parameter. Parameters allow us to: Avoid hard-coded messages and show real values which improve debugging and make error messages user-friendly.

Suppose we have a Bank Accounts table. A customer tries to withdraw money. If withdrawal amount is greater than available balance, the system must: not allow the transaction and show a clear error message with values.

See the demo.

Creating a table and inserting few records into this table.

CREATE TABLE BankAccounts (     AccountID INT PRIMARY KEY,     CustomerName varchar(50),     Balance   INT );  INSERT INTO BankAccounts VALUES (101,'Bagesh Kumar Singh', 5000), (102,'Amit Kumar', 3000);

See the records in this table.

select * from BankAccounts

Now we are creating a SP which is used to withdraw the amount. 

CREATE PROCEDURE WithdrawAmount (     @AccountID INT,     @WithdrawAmount INT ) AS BEGIN     DECLARE @CurrentBalance INT,                                            @CustomerName varchar(50);      SELECT @CurrentBalance = Balance,                                 @CustomerName = CustomerName     FROM BankAccounts     WHERE AccountID = @AccountID;      IF @WithdrawAmount > @CurrentBalance     BEGIN         RAISERROR (             'Dear , %s you does not have sufficient balance. your current vailable balance : %d, and you are trying to withdrawal: %d',             16,             1,                                            @CustomerName,             @CurrentBalance,             @WithdrawAmount         );         RETURN;     END      UPDATE BankAccounts     SET Balance = Balance - @WithdrawAmount     WHERE AccountID = @AccountID; END;

 SP is created successfully. Now we are running this SP.

EXEC WithdrawAmount     @AccountID = 101,     @WithdrawAmount = 6000;

See the result. 

In SQL Server, the RAISERROR statement allows us to pass up to 20 parameters.

Parameters are used to replace placeholders in the error message string.

Placeholders look like:

Ø  %d → integer

Ø  %s → string

Ø  %f → float

Placeholders must match parameter data types and severity must be between 0–25 and RAISERROR is deprecated.

To raise meaningful, dynamic business errors in SQL Server using custom messages with severity and runtime values.