How to Validate Data Within a Stored Procedure

Introduction

There are many situation in which we need to do validation in our stored procedure. We can do validation before insert, delete and update operations in stored procedures. Setting validation in a stored procedure on insert, delete and update operation is known as data validation.

• The CREATE PROC statement is used to create a stored procedure.
• A CREATE PROC statement must be first statement in the batch.
• We can create a temporary stored procedure in SQL server. In a temporary stored procedure we use the # sign as a prefix for the name in a local stored procedure and the ## syntax as the prefix for the name of a global stored procedure.
• You can pass one or more parameters in a stored procedure from the calling program of the stored procedure.
• The WITH RECOMPILE statement forces the stored procedure to be recompiled every time it is used in an application. It reduces system performance so avoid it.
• The WITH ENCRYPTION statement in the stored procedure prevents users from viewing the code of the stored procedure.
• To declare a parameter in the stored procedure we use the @ sign before the name of the parameter; then it's a data type.
• The Input parameter is used to accept values from the calling program.
• If a stored procedure returns a value to the calling program then we need to declare a variable in the calling program.
• The DECLARE clause is used to declare a variable in the calling program.
• To check validation we use IF ELSE statement exists function.
• We use RAISEERROR to explicitly generate an error in our stored procedure. 

Statement that creates the mcninvoice table:

Statements that insert data into the mcninvoice table:

Statement that is used to fetch data from the mcninvoice table:

Statement that creates a stored procedure with validation:

Statement that used the stored procedure and inserts data into the mcninvoice table:

Another statement that uses a stored procedure and inserts data into the mcninvoice table:

Another statement that uses a stored procedure and inserts data into the mcninvoice table but fails to insert data:

Statement that shows data inserted by a stored procedure: