Advertisement

Latest Post

Thursday, 1 December 2016

What is stored procedure? Advantages of Using Stored Procedures

What is stored procedure?
Stored Procedure is a set of SQL statements or queries with name that are store and manage in database. It is compile queries that can be useful to manipulate the data with fast processing to the end users.

Example: - following are the examples

1:- Example for insert statements

              create procedure sp_your_proc_name
(
 @customerId int,
 @customerName varchar(50),
 @customerAge  int,
 @customerCity Varchar(50)
)
as begin
insert into tableName(customerId, customerName, customerAge , customerCity) values(1,’alok’,30,’Noida’)
end
2:- Example for SELECT statements

Create Proc sp_your_proc_name
(
@intput_parameter1 datatype,
@intput_parameter1 datatype
)
as
begin
       Select column1, column2, column3…..
       from tableName
       where  condtion_based_on_your_input_parameter
end


Advantages of Using Stored Procedures
1    
  • Easy to maintain Stored Procedures because it is stored in specific location.
  • Stored Procedures can be tested independent from application and easy  to troubleshoot the block of code.
  • Stored Procedures is Isolated from Business Rules
  • Stored procedures are cached on the server, so it is speed up and optimize query.
  • It is Utilization of Set-based Processing

How to Create a Stored Procedure?
You can create stored procedures via two ways. I have explained both ways in below:

1:- Using SQL Server Management Studio

To create a procedure in Object Explorer
  1. Connect to an instance of Database Engine and then expand that instance in Object Explorer. 
  1. Expand Databases, expand the database in which you want to create procedure, and then expand Programmability. 
  1. Right-click Stored Procedures and then click New Stored Procedure. 
  1. On the Query menu, click Specify Values for Template Parameters. 
  1. In the Specify Values for Template Parameters dialog box, enter the Author name, Create Date,Description,Procedure_name,@Param1 etc.  values for the parameters 
  1. Click OK.
  2. Replace the SELECT statement with the following statement in the Query Editor,:
Transact-SQL
SELECT Id, Name, Age, Address, Department, Gender
FROM [SQLCLR].[dbo].[Employee]
 

  1. F5 to Execute or click Execute. The procedure is created as an object in the database.
  2. To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.
  3. To run the procedure, Open Object Explorer, right-click the stored procedure that you created with assigned name and select Execute Stored Procedure.

2:- Using Transact-SQL 

1.       In Object Explorer, connect to an instance of Database Engine.
2.       From the File menu, click New Query.
3.       Copy and paste the following example into the query window and click Execute. This example creates the same stored procedure as above using a different procedure name.


USE Payroll; 
GO 
Create PROCEDURE [dbo].[SP_GetStudents]
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END
 
4.       To run the procedure, copy and paste the following example into a new query window and click Execute. Notice that different methods of specifying the parameter values are shown.

EXEC [dbo].[SP_GetStudents]

5.       Display result.

       
How to modify a stored procedure?

Same as there is two ways to modify the stored procedure. Following, i am explaining both ways.

 

1:- Using SQL Server Management Studio

To modify a procedure in Management Studio


1.       Connect to an instance of Database Engine and then expand that instance in Object Explorer.
2.       Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
3.       Expand Stored Procedures, right-click the procedure to modify, and then click Modify.
4.       Modify the text of the stored procedure.
5.       To test the syntax, on the Query menu, click Parse.
6.       To save the modifications to the procedure definition, on the Query menu, click Execute.
7.       To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.


2:- Using Transact-SQL

To modify a procedure in Query Editor

1.       Connect to an instance of Database Engine and then expand that instance in Object Explorer.
2.       Expand Databases; expand the database in which the procedure belongs. Or, from the tool bar, select the database from the list of available databases. For this example, select the Payroll database.
3.       On the File menu, click New Query.
4.       Copy and paste the following example into the query editor.

USE [Payroll]

IF OBJECT_ID ( 'dbo.SP_GetStudents', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.SP_GetStudents; 
GO 
CREATE PROCEDURE dbo.SP_GetStudents
WITH EXECUTE AS CALLER 
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END 

5.       On the File menu, click New Query.
6.       Copy and paste the following example into the query editor

USE [Payroll]

ALTER PROCEDURE [dbo].[SP_GetStudents]
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END
  
7.       To save the modifications to the procedure definition, on the Query menu, click Execute.
8.       To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.
9.       To run the modified stored procedure, execute the following example.

  
How to call a function in Procedure?

  Step1: create a function

              create function fn_SumOftwoNumber(@NumIst int,@NumpIInd int)
returns int
as begin
Declare @Result as int
Set @Result=@NumIst+@NumpIInd
return @Result
end

  Step2: Call a function in store procedure 

create procedure sp_Test_Callfunction
(
 @IstVar int,
 @IIndVar int
)
as begin
Declare @sp as int
set @sp=dbo. fn_SumOftwoNumber (@IstVar,@IIndVar)
print @sp
end

 Step3: Execute Store procedure for Output

    
      EXEC sp_Test_Callfunction  45,60

Step3:  Output: - 105


Government Jobs