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:
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.