How To Create Store Procedure In Standard Format

Hello Friend Today In This Blog I Will Show You How To Create Store Procedure In Mssql Using Different.Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples.

Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specificA stored procedure is a group of SQL statements that has been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. A stored procedures will reduce network traffic and increase the performance. If we modify a stored procedure all the clients will get the updated stored procedure.s. As I am most familiar with SQL Server I will use that as my samples.


A stored procedure is a group of SQL statements that has been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. A stored procedures will reduce network traffic and increase the performance. If we modify a stored procedure all the clients will get the updated stored procedure.

Sample of creating a stored procedure

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;
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.

A stored procedure allows faster execution.

If the operation requires a large amount of SQL code that 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 a 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.

A 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.

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 Server we have different types of stored procedures:

System stored procedures
User-defined stored procedures
Extended stored Procedures
System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables

Example: sp_helptext [StoredProcedure_Name]

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 the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database.


Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are depreciated for the reason it would be better to avoid using extended stored procedures.


Step 1 :Create database

Create database Youtube

Step 2:Create table

Here In This step We Will Create table.

Here In This Step We Required Four Column With Name vid,vname,vlocation and vpassword using Parameter int,varchar.


Create table viewer 
(
vid bigint,
vname varchar(50),
vpassword varchar(50),
vlocattion varchar(50)

)

Step 3:insert data using store procedure

Here In This Step We Will Create Insert Procedure By Passing Four Parameter By Using Insert Trigger Functionality. 


create procedure wb_insert_user
(
@vid bigint,
@vname varchar(50),
@vpassword varchar(50),
@vlocattion varchar(50)
)
as
begin
insert into viewer(vid,vname,vpassword,vlocattion)
values(@vid,@vname,EncryptByPassPhrase('Tutorial',@vpassword),@vlocattion)

end

Step 4:view data using store procedure

Here In This Step We Will Create View Procedure Using Select Trigger Functionality. 

create procedure wb_view_user
as
begin
select 
ISNULL(vid,'-')vid,
ISNULL(vname,'-')vname,
ISNULL(CONVERT(varchar(50),DecryptByPassphrase('Tutorial',vpassword)),'-')vpassword,
ISNULL(vlocattion,'-')vlocattion
from viewer
end

Step 5:update data using store procedure

Here In This Step We Will Create Update Procedure By Passing Four Parameter By Using Update Trigger Functionality. 

create procedure wb_update_user
(
@vid bigint,
@vname varchar(50),
@vpassword varchar(50),
@vlocattion varchar(50)
)
as
begin
update viewer
set
vid=@vid,
vname=@vname,
vpassword=EncryptByPassPhrase('Tutorial',@vpassword),
vlocattion=@vlocattion
where vid=@vid

end

Step 6:delete data using store procedure

Here In This Step We Will Create Delete Procedure By Passing oneParameter By Using Delete Trigger Functionality.

create procedure wb_delete_user
(
@vid bigint
)
as
begin
delete from viewer
where vid=@vid
end