Home > DeveloperSection > Articles > Detailed concept on Stored Procedure in SQL Server

Detailed concept on Stored Procedure in SQL Server


ASP.Net ASP.Net 
Ratings:
0 Comment(s)
 11375  View(s)
Rate this:

Detailed concept on Stored Procedure in SQL Server

Definition:

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

Another Definition

A stored procedure is a set of SQL commands that has been compiled and stored on the database server. Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. Stored procedures improve performance by reducing network traffic and CPU load.

It is used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.

Main Features:

·         It is in compiled form.

·         End-users may enter or change data but do not write procedures

·         Information is entered in a consistent manner.

·         Statements in a stored procedure only need to be written one time.

·         It improves performance by reducing network traffic and CPU load.

 

It same as user defined function (UDF’s) but there are some difference between them

Main Benefits of stored procedure:

 

Precompiled Execution: Stored procedure is stored in database as a compiled form. So it is compiled only one times. When stored procedure is called may times so it gives fast and better result.

Reduced server and client traffic so it reducing the network traffic and CPU load.

It removes the SQL Injection problem. Etc.

 

How to use the Stored Procedure in SQL Server

 

Select query in Stored Procedure

Step 1: we write this statement on SQL Server (in query window).

 

CREATE procedure [dbo].[ProcedureName]

@Parameter1 int,

@Parameter2 varchar(50)

as

select * from TableName

where TableFieldName1=@Parameter1 and TableFieldName2=@Parameter2

 

Note: where

1. ProcedureName show the name of procedure for example ProcCustomerInfo etc

2. Parameter1 and parameter 2 is declared variable and these variables assigned at runtime.

3. TableName indicates the Name of Table.

4. TableFieldName1 and TableFieldName2 show the column name(field or attribute name)of TableName

Step2: Now Compile the stored procedure by pressing the f5 (Function key) button. It show the message on message window as “Command(s) completed successfully.”;

Step3: how to call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file

 

//Connection Created here and making DataReader object dr

SqlCommand cmd = new SqlCommand("ProcedureName",con);//Procedure call in sql command

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Parameter1",UserValue1);//Set the parameter1 by user input value         

cmd.Parameters.AddWithValue("@Parameter2", UserValue2); );//Set the parameter2 by user input value

dr = cmd.ExecuteReader();//Execute the procedure

dr.Read();//Read the first row

//Access the value then close the DataReader and Connection

Insert Query in stored procedure

Step 1: we write this statement on SQL Server (in query window).

CREATE PROCEDURE [dbo].[ProcedureName] (

@Parameter1 varchar(50), @Parameter2 varchar(100)

AS INSERT INTO TableName VALUES(@Parameter1,@Parameter2)

 

Step2: Now Compile the stored procedure by pressing the f5 (Function key) button. It show the message on message window as “Command(s) completed successfully.”;

Step3: how call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file

 

SqlCommand command = new SqlCommand("ProcedureName",con);

command.CommandType = CommandType.StoredProcedure;         

command.Parameters.Add("@Parameter1", SqlDbType.VarChar).Value = UserInputValue1; //Assign the value

command.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the value

command.ExecuteNonQuery();//Execute the procedure return type integer

 

Update Query in stored procedure

Step 1: we write this statement on SQL Server (in query window).

CREATE PROCEDURE [dbo].[ProcedureName] (

@Parameter1 varchar(50), @Parameter2 varchar(100)

AS UPDATE TableName SET TableFieldName1=@Parameter1 WHERE TableFieldName2=@Parameter2

 

Step2: Now Compile the stored procedure by pressing the F5 (Function key) button. It show the message on message window as “Command(s) completed successfully.”;

Step3: how call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file

 

SqlCommand command = new SqlCommand("ProcedureName",con);

command.CommandType = CommandType.StoredProcedure;         

command.Parameters.Add("@Parameter1", SqlDbType.VarChar).Value = UserInputValue1; //Assign the value

command.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the value

command.ExecuteNonQuery();//Execute the procedure return type integer

 

Note: Similarly create for delete  as insert (created above)


Don't want to miss updates? Please click the below button!

Follow MindStick