Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to create a Store Procedure in SQL Server

Author: Faraz

In this article I will try to explain you about a store procedure, what are the advantages in using a store procedure and the default syntax of a store procedure.

A stored procedure is a query that is stored in a database on SQL server rather than being stored in the front end i.e., at the client side. It allows us to manage our database and users who were using that database efficiently and easily. Store Procedures reside on a server as a precompiled collection of SQL statements and optional control-of-flow statements stored under a unique name and processed as a unit.

 

Stored procedures with in a same database allow the user/developers to call and execute one store procedure into another. In store procedures you can declare variables, execute conditional statements, restrict users in doing unauthorized task (security checks), and manage transaction integrity.  They also allow users to manage program flow, logic and queries in a single store procedure.

 

Using store procedures you can pass values by parameter, get values from parameter (Output parameters), return single or multiple result sets and values.

 

Advantages in using Store Procedure:

 

Following are the advantages in using store procedures:

 

  • Allow user to execute multiple SQL statements in a single store procedure.
  • Allow user to call another store procedure with in a store procedure in a same database.
  • Execution of store procedures is faster than a single SQL statement. The reason is that store procedure is compiled when it is created means it resides on a server in a precompiled form.

Note: Functionality of a store procedure is depending on the features offered by our database.

 

Syntax of a Store Procedure:

 

Default syntax of our store procedure is as follows:

 

CREATE PROCEDURE dbo.ProcedureName

 

@variableName datatype,

@variablename datatype(length) = ’value’

 

 AS

 

/* Statements to be executed */

 

SET NOCOUNT ON

 

RETURN /* Variables */

 

GO /* execute procedure */

Article Comments
Please Give me Details about sql store procedure

Posted on 1/18/2007 1:40:09 AM by Sudhir Kumar jena

can you please tell me how to get complete information of a store procedure like name, return type, parameters list along with directions in SQL,
I have used sp_help command but it did'nt tell about the direction of parameters

Posted on 1/6/2008 9:29:54 AM by Wasif

A stored procedure is a set of sql statements that you assign a name to add store in database in compiled form so that you can share it between a number of program.
Why we are using the stored procuder becuse
They allow modular programming.
They can reduce Network traffic.
They allow faster Execution.
They can be used a security machanism.
There are different type of stored procedure

* Temporary stored procedure
* System stored procedure
* Automatically executing stored procedure
* user stored procedure

Posted on 4/16/2008 1:02:59 PM by javed isa

i want to know clear detail for stored procedure in SQL

Posted on 9/1/2008 1:54:12 AM by jayaprakash c

i want to know how to pass parameter in storeprocedure.

Posted on 9/27/2008 8:13:02 AM by kartika

SP is Better in Performance and Maintainance.
They eliminates to reparse and reoptimize each times they executed.
They Reduce the Network Traffic because SP is compiled once While Simple SQL Statement Compiled each times they are executed.

http://www.dotnet-magic.blogspot.com/

Posted on 11/14/2008 9:38:29 PM by ketan

i want to know clear detail for stored procedure in SQL &
view plz tell me about this.

Posted on 9/17/2009 1:02:36 AM by Dharmendra Sharma

i want know about store procedure in vb.net with sql

Posted on 5/31/2010 5:58:32 AM by rakesh de

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Creating a Connection to the Data Source Using Sql Server

Disclaimer - Privacy
© 2002-2017 DevASP.net