How to Write stored procedures in sqlserver

Original Link

Http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

Stored procedures are defined as logical SQL statements in sqlserver, which implement specific tasks. There are many benefits to using stored procedures. The primary advantage is to improve database performance.

Benefits:

1. Reduces the amount of information sent to the database, which is very important when the network bandwidth is limited. If you run a cyclic SQL statement on the database through the network. An error occurs if the network connection is disconnected or the rollback statement is not executed.
2. Compile the stored procedure once it is created. However, the SQL statement must be compiled once each time it is delivered for execution, even though the SQL statements delivered each time are the same.
3. This improves the reusability of SQL code, because multiple clients can call the same processing process to avoid writing the same SQL statement each time. It also reduces the development time.
4. This helps improve security. We can grant the permissions of a stored procedure to a specified user to avoid granting the permissions of the tables involved in the stored procedure.
5. Sometimes it is safer to use stored procedures to encapsulate business logic. If you need to change it, you only need to change it on the server without making any changes to the Web.

Assume that a table named tbl_students has the following structure:

CREATE TABLE  tbl_Students(    [Studentid] [int] IDENTITY(1,1) NOT NULL,    [Firstname] [nvarchar](200) NOT  NULL,    [Lastname] [nvarchar](200)  NULL,    [Email] [nvarchar](100)  NULL)

Add the following data to the table above.

insert into tbl_Students n(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 we start to write the stored procedure. The first step is to create the Stored Procedure statement.

Create Procedure Procedure-name (Input parameters ,Output Parameters (If required))AsBegin     Sql statement used in the stored procedureEnd

Create a stored procedure with studentid as the parameter and return the content of studentname as follows:

/*  Getstudentname is the name of the stored procedure*/Create  PROCEDURE Getstudentname(@studentid INT                   --Input parameter ,  Studentid of the student )ASBEGINSELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid END

We can also use student name as the output parameter to output the Code as follows:

 /* GetstudentnameInOutputVariable is the name of the stored procedure whichuses output variable @Studentname to collect the student name returns by thestored 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)ASBEGINSELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentidEND

In sqlserver, you can modify the content by modifying keywords. If we want to add the email address information in the above stored procedure, we need to introduce another output parameter as follows:

/* Stored Procedure GetstudentnameInOutputVariable is modified to collect theemail 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)ASBEGINSELECT @studentname= Firstname+' '+Lastname,     @StudentEmail=email FROM tbl_Students WHERE studentid=@studentidEND

Note that not all stored procedures need to write return values. For example, some insert and delete update statements are as follows:

 

/*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

SQL Server executes the Stored Procedure

The stored procedure in SQL Server can be executed using execute or exec. For example, getstudentname

Execute getstudentname 1 exec getstudentname 1

Execute the stored procedure with the output parameter. First, declare the variable to hold the parameter, for example

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the StudentnameDeclare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the StudentemailExecute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail outputselect @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure

Jieshu

We can say that the stored process can not only enhance code reusable execution, but also improve database performance by reducing network data!