Create table first. Here I have created StoreInfo table with above column.
What is stored Procedure?
A stored procedure is a group of sql statements that has been created
and stored in the database. Stored procedure will accept input
parameters so that a single procedure can be used over the network by
several clients using different input data. Stored procedure will reduce
network traffic and increase the performance. If we modify stored
procedure all the clients will get the updated stored procedure.
Advantages of using stored procedures
a) Stored procedure allows modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program.
b) Stored Procedure allows faster execution.
If the operation requires a large amount of SQL code is performed
repetitively, stored procedures can be faster. They are parsed and
optimized when they are first executed, and a compiled version of the
stored procedure remains in memory cache for later use. This means the
stored procedure does not need to be reparsed and reoptimized with each
use resulting in much faster execution times.
c) Stored Procedure can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be
performed through a single statement that executes the code in a
procedure, rather than by sending hundreds of lines of code over the
network.
d) Stored procedures provide better security to your data
Users can be granted permission to execute a stored procedure even if
they do not have permission to execute the procedure's statements
directly.
In SQL we are having different types of stored procedures are there
a) System Stored Procedures
b) User Defined Stored procedures
c) Extended Stored Procedures
System Stored Procedures:
System stored procedures are stored in the master database and these
are starts with a sp_ prefix. These procedures can be used to perform
variety of tasks to support sql server functions for external
application calls in the system tables
Ex: sp_helptext [StoredProcedure_Name]
User Defined Stored Procedures:
User Defined stored procedures are usually stored in a user database
and are typically designed to complete the tasks in the user database.
While coding these procedures don’t use sp_ prefix because if we use the
sp_ prefix first it will check master database then it comes to user
defined database
Extended Stored Procedures:
Extended stored procedures are the procedures that call functions
from DLL files. Now a day’s extended stored procedures are depreciated
for that reason it would be better to avoid using of Extended Stored
procedures.
Insert, Update, delete and display using single Stored Procedure
CREATE PROCEDURE [dbo].[StoreInfo_Pro]
(
@StoreID Bigint,
@StoreName varchar(50),
@Address varchar(100) ,
@StateCd varchar(50),
@ZipCd varchar(5),
@Phone varchar(50),
@Action varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
if(@Action = 'Display')
BEGIN
SELECT * FROM StoreInfo;
END
else if(@Action = 'Add')
BEGIN
INSERT INTO StoreInfo(StoreID,StoreName,Address,StateCd,ZipCd,Phone) VALUES (@StoreID,@StoreName,@Address,@StateCd,@ZipCd,@Phone)
END
else if(@Action = 'Update')
BEGIN
UPDATE StoreInfo Set StoreName=@StoreName,Address=@Address,StateCd=@StateCd,ZipCd=@ZipCd,@Phone=@Phone WHERE StoreID=@StoreID
END
else if(@Action = 'Delete')
BEGIN
DELETE FROM StoreInfo WHERE StoreID=@StoreID
END
END
This is the stored procedure we can use for insert, update, delete, select by passing one extra parameter Action.
1. If Action value is Add, insert query will be executed.
2. If Action value is Display, select query will be executed.
3. If Action value is Update, Update query will be executed.
4. If Action Value is Delete, delete query will be executed.
