What is Stored Procedures

What is Stored Procedures

A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. Procedures resemble constructs in other programming languages because they can:

1. Accept input parameters and return multiple values in the form of output parameters to the calling program.
2. Contain programming statements that perform operations in the database. These include calling other procedures.
3. Return a status value to a calling program to indicate success or failure (and the reason for failure).

Advantages or merits or Characteristics of Stored Procedures:-There are lot of advantages of Stored Procedures which are following:-

1. Stronger security :-By Stored Procedures we can achived higher security.
   
   Example:- Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers
2. Easier maintenance :- When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.
3. Reuse of code :-The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.
4. Improved performance :-By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
5. Reduced server/client network traffic :-The commands in a procedure are executed as a single batch of code. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.
6. Schema changes in general are easier, because the code that accesses your tables is all in one place, instead of being spread out through your entire application.
7. You can use Stored Procedures to avoid SQL injection attacks (pure string concatenation is a bad, bad idea)
8. You can only use table-valued parameters with Stored Procedures
9. It's easier to add a little procedural logic (if/while/variables/etc) to Stored Procedures
10. 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.

Types of Stored Procedures:-

1. User-defined Stored Procedures :-A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. The procedure can be developed in either Transact-SQL or as a reference to a Microsoft .NET Framework common runtime language (CLR) method.
2. System-defined Stored Procedures :-System procedures are included with SQL Server. They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures
3. Extended User-Defined Stored Procedures :-Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL Server can dynamically load and run.
4. Temporary Stored Procedures :- Temporary procedures are a form of user-defined procedures. The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb.
   There are two types of Temporary Stored Procedures :-
   • Local Temporary Stored Procedures :- Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed.
   • Global Temporary Stored Procedures :- Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

Note :-There is at least one table is required for Stored Procedures. Beaused we can perform any operations on table.

Now, in programming we are using generally two types of Stored Procedures.

1. Single type Stored Procedure :- In this Stored Procedure we can able to perform only single operation by a Stored Procedure. Example:- Insert, Update, Delete, Search etc..
2. Multiple type Stored Procedure :-By this Stored Procedures we can able to perform multiple oprations by single Stored Procedure

  

CREATE DATABASE dbSantoshTest

use dbSantoshTest

CREATE TABLE tblName
(
@Column1 datatype, @Column2 datatype, @Column3 datatype,.....
)

CREATE PROC or PROCEDURE Proc_Name
@Column1 datatype, @Column2 datatype, @Column3 datatype,.....
AS
Begin
--Write query here, which types of operation, do you want to perform.
End

The example of Insert, Update, Delete and Search Stored Procedure in SQL SERVER database. For this we open SQL SERVER and write the following code:-

First of all we create Database and give name dbSantoshTest as following:-

  

CREATE DATABASE dbSantoshTest

Use dbSantoshTest

CREATE TABLE tblStudent
(
Name varchar(100), Rollno int primary key,Branch varchar(50)
)

A. Stored Procedure for Insert or Save :-

  

CREATE PROC USP_InserttblStudent  
@Name varchar(100),@Rollno int, @Branch varchar(50)  
  
AS  
BEGIN  
 begin try  
  begin tran  
 Insert into tblStudent values(@Name,@Rollno,@Branch)  
 print 'Student record has been inserted!'  
 commit tran  
 end try  
   
 begin catch  
 print 'Student record has not been inserted!'  
 rollback  
 end catch  
  
END

    
   --------------Save some records in tblStudent table----------------------

USP_InserttblStudent 'Santosh kumar singh',1001,'IT'
USP_InserttblStudent 'Manorya Arya',1002,'MCA'
USP_InserttblStudent 'Sarika',1003,'ECE'
USP_InserttblStudent 'Abhinav kumar',1004,'IT'
USP_InserttblStudent 'Pushpanjali kuamri',1005,'MCA'

B. Stored Procedure for Update or Edit :-

  

CREATE PROC USP_UpdateStudent 
@Name varchar(100),@Rollno int,@Branch varchar(50) 
  
AS  
BEGIN  
  
 begin try  
  begin tran  
  Update tblStudent set Name=@Name,Branch=@Branch where Rollno=@Rollno  
    
  print 'Student record has been updated!.'  
  commit tran  
 end try  
   
 begin catch  
  print 'Student record has not been updated!.'  
 rollback  
 end catch  
   
END


---------------------Update first record in tblStudent table---------------------
    USP_UpdateStudent 'Santosh Kumar Singh',1001,'Information Technology' 

C. Stored Procedure for Delete :-

  

CREATE PROC USP_DeleteStudent 
@Rollno int
AS  
BEGIN  
  begin try  
   begin tran  
   Delete from tblStudent where Rollno=@Rollno  
   print 'Student record has been deleted!.'  
   commit tran  
     
  end try  
    
  begin catch  
   print 'Student record has not been deleted!.'  
   rollback  
  end   catch  
   
   
END

------------Delete third record from tblStudent------------

    USP_DeleteStudent 1003

D. Stored Procedure for Search All Record :-

  

CREATE PROC USP_GetStudent  
AS  
BEGIN  
  Select * from tblStudent  
END

Now, how to use Stored Procedure in ASP.NET for this we go to Types:-

1. Single type Stored Procedure
2. Multiple type Stored Procedure