Crud operation using single procedure
We can use single procedure to perform insert , update, delete and select operation
We can write a single procedure to perform insert, update and delete operation. We don't need to write different procedures separately to perform crud operation.
STEP1: To understand this first of all we have to create a table product. In the product table, we will use three fields [productid], [productname] and [price].
STEP2: In step 2 we will create a single procedure to perform crud functionality. In this procedure, we will use an extra parameter "status".
create procedure usp_crudoperation (@productid int,@productname varchar(50),@price int, @status varchar(50))
as begin
if @status = 'insert'
begin
insert into product(productname,price) values ( @productname ,@price)
end
if @status = 'update'
begin
update product set productname=@productname,price=@price where productid=@productid
end
if @status = 'delete'
begin
delete product where productid=@productid
end
if @status = 'select'
begin
select * from product
end
end