Creating Stored Procedure in SQL Server

Stored procedure is a group of SQL statements stored on the server.It is similar to a batch file as it just a group of SQL statements which are executed as a unit.

The SQL statements in a stored procedure are written in T SQL since it is the version of SQL used in SQL Server.Since stored procedures are stored on the server so it provides a means to reuse the SQL statements.Instead of duplicating the SQL statements we write the statements once and reuse them whenever required.

Some of advantages offered by the stored procedures are:

• Since the SQL statements in a procedure are tested so it reduces the chances of errors.
• Users are required to just call the procedure instead of directly executing the SQL statements.This creates an abstraction for the users.Stored procedure can be modified easily without any impact on how it is used.
• Stored procedures are precompiled and stored on the server.This provides faster execution speed specially when the procedure is called multiple times.
• Since stored procedures are stored on the server hence SQL statements are not required to be sent to the server.Only the required information such as the input parameters are passed to the server.So network traffic is reduced.

Lets see how we can create and call the stored procedure

We create a stored procedure by using the following syntax

create proc|procedure ProcedureName
@Parameter datatype[=default value]
as
Body of the Stored Procedure

We will be using the following Users table for the examples here

CREATE TABLE [dbo].[Users](
[UserName] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[Id] [int] NOT NULL
) ON [PRIMARY]

To return all the data from the Users table we can create the  following procedure:

create proc spReturnAllUsers
as
select * from [dbo].[Users]
To execute the above procedure we can use the following statement
exec spReturnAllUsers

Passing input parameters

For returning data for a specific user we create the following procedure.Here we are passing @UserId as a input parameter.

create proc spGetUserById
@UserId int
as
SELECT * FROM Users
WHERE ID=@UserId

To execute the procedure we use the following command

exec spGetUserById 1

Since this procedure expects a argument so we need to supply the argument value to call the procedure.In the above statement we are passing the value 1 to the spGetUserById procedure.

Default value of a Parameter

We can also assign a default value to the parameter.We can then call the procedure without supplying any value for the default parameter.We can create a procedure with a default value as:

create proc spGetUserById
@UserId int=1
as
SELECT * FROM Users
WHERE ID=@UserId

Now we can call the procedure without providing any value for the parameter:

exec spGetUserById

Since we are not providing any value for the @UserId parameter so the default value 1 is used.

Returning values using Output parameters

To return values from the stored procedure we use output parameters.Output parameter is declared like a input parameter but has the output keyword after the parameter declaration.So to declare output parameter in the spGetUserById procedure we use the following T-SQL script:

ALTER proc [dbo].[spGetUserById]
@UserId int,
@UserName varchar(50) output
as
SELECT @UserName=UserName from Users where Id=@UserId
SELECT * FROM Users
WHERE ID=@UserId

We have declared the @UserName output parameter.This means that the procedure can assign some value to the parameter.After the procedure executes the value of the output parameter is available in the calling code.

To call the spGetUserById procedure we can use the following SQL:

DECLARE @Name varchar(50);
EXEC [dbo].[spGetUserById] 1,@UserName=@Name OUTPUT
print @Name

We have declared a variable Name.Data Type of Name is same as the output parameter which is varchar.After the procedure executes we are printing the value of Name variable.

Altering an existing procedure

To alter an existing procedure we use the alter proc statement instead of create proc.So for modifying the spGetUserById we have created earlier we can use the alter proc statement as:

alter proc spGetUserById
@UserId int=1
as
SELECT UserName,Id FROM Users
WHERE ID=@UserId

So by creating Stored Procedure in SQL Server we reduce duplication of sql and there are less chances of using incorrect SQL.

Follow on: