A Tutorial on SQL Server Stored Procedure

 

  • A stored procedure is a SQL code that can be saved and reused when ever required.
  • Stored procedures are a batch of SQL statements that can be executed and reused. Most major DBMs support stored procedures, but, not all do. You need to examine this with your respective DBMS help documentation for specifications.
  • In case of any query which is required to be re-written again-and-again, that can be written as stored procedure and saved. Then that respective stored procedure can be called and executed.
  • In addition to run the same SQL code over and over again you must also have the ability to pass parameters to the respective stored procedure.
  • Depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed as an argument.
  • Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.

An easy aproach to understand the SQL Server Stored Procedure with example

A stored procedure is a group of SQL statements compiled into a single execution plan.

1. Create once and call it n number of times
2. It minimizes the network traffic

For Example:

creating a stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;

SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO

Alter or modify a stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;

SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO

Drop or delete a stored procedure:

DROP PROCEDURE GetEmployee

Various ways of creating SQL Server stored procedures

There are various options that can be used to create stored procedures. In this tutorial we are going to discuss creating a simple stored procedure for more advanced options which can be used while creating stored procedures in different scenarios.

Description

Few topics we are going to describe here include:

  • Creating a simple stored procedure
  • Using input parameters
  • Using output parameters
  • Using Try Catch

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.