SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the result set if any.
Benefit of Using Store Procedure:
It can be easily modified: SQL Server Stored procedures Stores the code in the database. so, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.
Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code.
Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again.
Security: Stored procedures reduce the threat by eliminating direct access to the tables. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible.
Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time.
Create Store Procedure without Parameter:
create store procedure which returns the join of customer table and internet_sales table:
create procedure cus_sale_wop as
begin
set nocount on
select top 10 * from [dbo].[DimCustomer] c join FactInternetSales f on c.CustomerKey=f.CustomerKey
end
exec cus_sale_wop
Create a Store Procedure with Parameter:
There are two types of parameter in store procedure
- Input Parameter
- Output Parameter
Input Parameter:
create procedure cus_sale_wp(@c_key int)
as
begin
set nocount on
select top 10 * from [dbo].[DimCustomer] c join FactInternetSales f on c.CustomerKey=f.CustomerKey
where c.CustomerKey=@c_key
end
exec cus_sale_wp 11005
Input Default Parameter:
create procedure cus_sale_wdp(@c_key int =11005)
as
begin
set nocount on
select top 10 * from [dbo].[DimCustomer] c join FactInternetSales f on c.CustomerKey=f.CustomerKey
where c.CustomerKey=@c_key
end
exec cus_sale_wdp
Out Put Parameter:
create procedure cus_sale_wopp(@c_key int,@c_name varchar(100) output)
as
begin
set nocount on
select @c_name=firstname+lastname from [dbo].[DimCustomer] where CustomerKey=@c_key
end
declare @c_name varchar(100)
exec cus_sale_wopp 11000 ,@c_name output
select @c_name
Encrypted Store Procedure:
create procedure cus_sale_we(@c_key int)
with encryption
as
begin
set nocount on
select firstname+lastname from [dbo].[DimCustomer] where CustomerKey=@c_key
end
exec cus_sale_we 11000
sp_helptext cus_sale_we
When we try to view the code of the SQL Server stored procedure using sp_helptext, it returns “The text for object ‘GetEmployees’ is encrypted.”
Temporary Store Procedure: These procedures are created in the tempdb database.
- Local Store Procedure
- Global Store Procedure
Local Store Procedure: These are created with # as prefix and can be accessed only in the session where it created. This procedure is automatically dropped when the connection is closed.
create procedure #cus_sale_we(@c_key int)
with encryption
as
begin
set nocount on
select firstname+lastname from [dbo].[DimCustomer] where CustomerKey=@c_key
end
exec #cus_sale_we 11000
Global Temp Store Procedure: These procedures are created with ## as prefix and can be accessed on the other sessions as well. This procedure is automatically dropped when the connection which is used to create the procedure is closed.
create procedure ##cus_sale_we(@c_key int)
with encryption
as
begin
set nocount on
select firstname+lastname from [dbo].[DimCustomer] where CustomerKey=@c_key
end
exec ##cus_sale_we 11000
Modifying Store Procedure: Use the ALTER PROCEDURE statement to modify the existing stored procedure.
alter procedure cus_sale_we(@c_key int)
with encryption
as
begin
set nocount on
select firstname+lastname from [dbo].[DimCustomer] where CustomerKey=@c_key
end
Rename Store Procedure:
sp_rename 'old_procedure','new_procedure'
