Validation into Store Procedure in SQL Server

Validation in stored procedures is like any normal programming validations.

Here are the examples:

1. Always the balance should be greater than 1000

Declare @balance decimal(15,3)
SELECT @balance = balance from Account where customerid=5

IF (@balance < 1000)
BEGIN
RAISEERROR("Balance is less than 1000")
RETURN
END

2. Check if selected date should be greater than equal to today's date

SET @SelectedDate = '12/12/2010'

IF (@SelectedDate < GetDate())
BEGIN
RAISEERROR("Date error")
Return
END

RaiseError is the method to raise an error and abrupt from executing the next statements in sql stored procedure and it is propagated to the client. So, we need to catch the errors either raised by our own logic or by the sql server and display the friendly message to the user and log the error to find the issue.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Validation in stored procedures is like any normal programming validations.

Here are the examples:

1. Always the balance should be greater than 1000

Declare @balance decimal(15,3)
SELECT @balance = balance from Account where customerid=5

IF (@balance < 1000)
BEGIN
RAISEERROR("Balance is less than 1000")
RETURN
END

2. Check if selected date should be greater than equal to today's date

SET @SelectedDate = '12/12/2010'

IF (@SelectedDate < GetDate())
BEGIN
RAISEERROR("Date error")
Return
END

RaiseError is the method to raise an error and abrupt from executing the next statements in sql stored procedure and it is propagated to the client. So, we need to catch the errors either raised by our own logic or by the sql server and display the friendly message to the user and log the error to find the issue.
0