[أبو عمر]

How to Write a Stored Procedure in SQL Server
Suppose there is a table called 

كود :

tbl_Students 

whose structure is given below:

CREATE TABLE  tbl_Students


(

   [Studentid] [int] IDENTITY(1,1) NOT NULL,

   [Firstname] [nvarchar](200) NOT  NULL,

   [Lastname] [nvarchar](200)  NULL,

   [Email] [nvarchar](100)  NULL

)

Support we insert the following data into the above table:

Insert into tbl_Students (Firstname, lastname, Email)
Values('Vivek', 'Johari', 'vivek@abc.com')



Insert into tbl_Students (Firstname, lastname, Email)

Values('Pankaj', 'Kumar', 'pankaj@abc.com')



Insert into tbl_Students (Firstname, lastname, Email)

Values('Amit', 'Singh', 'amit@abc.com')



Insert into tbl_Students (Firstname, lastname, Email)

Values('Manish', 'Kumar', 'manish@abc.comm')



Insert into tbl_Students (Firstname, lastname, Email)

Values('Abhishek', 'Singh', 'abhishek@abc.com')

Now, while writing a Stored Procedure, the first step will be to write the 

كود :

Create Procedure 

statement as the first statement:

Create Procedure Procedure-name 
(

Input parameters ,

Output Parameters (If required)

)

As

Begin

    Sql statement used in the stored procedure

End

Now, suppose we need to create a Stored Procedure which will return a 

كود :

student

 name whose 

كود :

studentid

is given as the input parameter to the stored procedure. Then, the Stored Procedure will be:

/*  Getstudentname is the name of the stored procedure*/


Create  PROCEDURE Getstudentname(



@studentid INT                   --Input parameter ,  Studentid of the student 



)

AS

BEGIN

SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid 

END

We can also collect the 

كود :

student 

name in the output parameter of the Stored Procedure. For example:

/* 
GetstudentnameInOutputVariable is the name of the stored procedure which

uses output variable @Studentname to collect the student name returns by the

stored procedure

*/



Create  PROCEDURE GetstudentnameInOutputVariable

(



@studentid INT,                       --Input parameter ,  Studentid of the student

@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword

)

AS

BEGIN

SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid

END

Note* */ is used to write comments in one or multiple lines
-- is used to write a comment in a single line
How to Alter a Stored Procedure in a SQL Server
In SQL Server, a stored procedure can be modified with the help of the 

كود :

Alter 

keyword. Now if we want to get 

كود :

student 

email address through the same procedure 

كود :

GetstudentnameInOutputVariable

. So we need to modify it by adding one more output parameter "

كود :

@StudentEmail 

" which is shown below:

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the

email address of the student with the help of the Alert Keyword

*/ 



Alter  PROCEDURE GetstudentnameInOutputVariable

(



@studentid INT,                   --Input parameter ,  Studentid of the student

@studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name

@StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email

)

AS

BEGIN

SELECT @studentname= Firstname+' '+Lastname, 

   @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid

END

Note: It is not necessary that a stored procedure will have to return. There can be a case when a stored procedure doesn't returns anything. For example, a stored procedure can be used to 

كود :

Insert

, 

كود :

delete 

or

كود :

update 

a SQL statement. For example, the below stored procedure is used to 

كود :

insert 

value into the table

كود :

tbl_students

.

/*
This Stored procedure is used to Insert value into the table tbl_students. 

*/



Create Procedure InsertStudentrecord

(

@StudentFirstName Varchar(200),

@StudentLastName  Varchar(200),

@StudentEmail     Varchar(50)

) 

As

Begin

  Insert into tbl_Students (Firstname, lastname, Email)

  Values(@StudentFirstName, @StudentLastName,@StudentEmail)

End

Execution of the Stored Procedure in SQL Server
Execution of the Stored Procedure which doesn't have an Output Parameter
A stored procedure is used in the SQL Server with the help of the "

كود :

Execute

" or "

كود :

Exec

" Keyword. For example, if we want to execute the stored procedure "

كود :

Getstudentname

", then we will use the following statement.

Execute Getstudentname 1
Exec Getstudentname 1

Execution of the Stored Procedure using the Output Parameter
If we want to execute the Stored procedure "

كود :

GetstudentnameInOutputVariable

" , then we first need to declare the variable to collect the output values. For example:

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname

Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail

Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output

select @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure



المصدر
http://www.codeproject.com/Articles/1268...ure-in-SQL