Post
Ask Question

How to Validate Data Within a Stored Procedure

Introduction

In this article, we will learn about how to validate data within Stored Procedures in SQL Server.

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

  • The CREATE PROC statement is used to create a stored procedure.
  • A CREATE PROC statement must be the first statement in the batch.
  • We can create a temporary stored procedure in the 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 the IF ELSE statement exists function.
  • We use RAISEERROR to explicitly generate an error in our stored procedure. 

A statement that creates the mcninvoice table.

create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar (15) ,
invoicetotal money,
paymenttotal money,
creadittotal money
)

Statements that insert data into the mcninvoice table.

insert into MCNINVOICES VALUES (20,'E001',100,100,0.00);
insert into MCNINVOICES VALUES (21,'E002',200,200,0.00);
insert into MCNINVOICES VALUES (22,'E003',500,0.00,100);
insert into MCNINVOICES VALUES (23,'E004',1000,100,100);
insert into MCNINVOICES VALUES (24,'E005',1200,200,500);
insert into MCNINVOICES VALUES (25,'E006',1250,250,200);

A statement that is used to fetch data from the mcninvoice table.

SELECT * FROM MCNINVOICES;

A statement that creates a stored procedure with validation.

create proc spvalidate
@vendorid int,
@invoiceno varchar (15),
@invoicetotal money,
@paymenttotal money,
@creadittotal money
as
if exists (select * from vendors where vendorid=@vendorid)
begin
insert into mcninvoices
values
(
@vendorid,
@invoiceno,
@invoicetotal,
@paymenttotal,
@creadittotal
)
end
else
begin
raiserror( 'It is not a valid id',1,1)
return -100
end

A statement that used the stored procedure and inserts data into the mcninvoice table.

declare @ret_var int
exec @ret_var = spvalidate
20,'E10',9000,2000,1000
print 'Return code was :'+ convert (varchar, @ret_var)

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

declare @ret_var int
exec @ret_var = spvalidate
30,'E11',8000,3000,2000
print 'Return code was :'+ convert (varchar, @ret_var)

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

declare @ret_var int
exec @ret_var = spvalidate
50,'E12',7000,1500,2500
print 'Return code was :'+ convert (varchar, @ret_var)

A statement that shows data inserted by a stored procedure.

select * from mcninvoices;

Conclusion

In this article, we learned about how to validate data within a Stored Procedure.


Similar Articles