Store Procedure in ADO.NET.

Store Procedure:

A stored procedure is a database object that contains one or more SQL statements. Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database.

If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment (I am a SQL Server kind of emp, and run stored procedures from the Query Analyzer), using the exec command.

Classification Store Procedure:

The classification of stored procedures is depends on the Where it is Stored. Based on this you can divide it in 4 sections. that are:

• System Stored Procedure.

• Custom Store Procedure.

• Extended Store Procedure.

System Store Procedure:

System stored procedures are mainly used for administrating, assisting, configuring and monitoring the SQL server. For example, you can view the contents of the stored procedure by calling "sp_helptext [StoredProcedure_Name]".

Custom Store Procedure:

These are the stored procedures we write. There are several advantages in writing our own stored procedures. We are able to write complex and nested statements with less effort.

Extended Store Procedure:

Extended stored procedures are used to access SQL server by using dlls.

Advantages of Store Procedure:

• A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

• Stored Procedures are coding block in database server. It is pre compiled entity i.e. it is compiled at once and can be used again and again.

• With help of stored procedure a group of SQL statements can be executed sequentially.

• To supply data to the procedure we must have to use parameters in procedure.

• Stored procedures use parameters mapping concept.

• In parameter mapping front end and procedure parameters names, type and direction must be same and where front-end parameter length should be less than or equal to procedure parameter length (than only can map parameters).

• To return any value from procedure we use return statement.

Create a simple Store Procedure:

There are various Steps to create a simple procedure in Sql 2008:

• in step first->open Sql sever->create table
  
  
  

• in Step two write these code
  
  
  

• for Executing procedure write EXEC procedure name.