SQL Stored Procedures

A stored procedure is a named block of SQL code.

Stored procedures can be reused and executed anytime.

To create a stored procedure use the CREATE PROCEDURE command.

Stored procedures are executed with the EXECUTE command.

Example

#

This stored procedure returns a list of customers who have ordered products from suppliers in their own country. This procedure accepts a Country argument.

CREATE PROCEDURE InCountryCustomers
 @Country NVARCHAR(40)
AS
 BEGIN
  SELECT DISTINCT FirstName, LastName, C.Country
    FROM Customer C
    JOIN [Order] O ON C.Id = O.CustomerId
    JOIN OrderItem I ON O.Id = I.OrderId
    JOIN Product P ON I.ProductId = P.Id
    JOIN Supplier S ON P.SupplierId = S.Id
   WHERE C.Country = @Country 
     AND S.Country = C.Country
 END

The above procedure is a reusable SELECT statement with 5 table JOINs.

It offers flexibility by providing a country parameter.


Executing the Procedure

#

To run a procedure use the EXECUTE command, like so:

EXECUTE InCountryCustomers 'France'

This returns all customers in France that ordered products from French suppliers.

Result:  4 records
FirstName LastName Country
Dominique Perrier France
Frédérique Citeaux France
Laurence Lebihan France
Martine Rancé France

Using Stored Procedures

A stored procedure is a T-SQL program with SQL statements and optional logic.

A stored procedure represents reusable SQL code that can run repeatedly.

Stored procedures are database objects stored on the database server.

Stored procedures can accept parameters.

Syntax

Syntax to create a stored procedure.

CREATE PROCEDURE procedure_name 
AS
 BEGIN
  sql_statement
 END

procedure_name -- the name assigned to the procedure.

sql_statement -- any valid SQL query or block of T-SQL (Transact-SQL) code.

Syntax to run a stored procedure.

EXECUTE procedure_name

Or, use the shorthand.

EXEC procedure_name

Syntax to change a stored procedure.

ALTER PROCEDURE procedure_name 
AS
 BEGIN
  sql_statement
 END

This effectively overwrites the prior version of the stored procedure.

Syntax to remove a stored procedure.

DROP PROCEDURE procedure_name 

More Examples

CREATE PROCEDURE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Create a procedure that lists all suppliers and their products.
CREATE PROCEDURE SupplierProducts
AS
 BEGIN
  SELECT CompanyName, ProductName, UnitPrice, Package
    FROM Supplier S
    JOIN Product P ON P.SupplierId = S.Id
   ORDER BY CompanyName
 END 
Result:  Procedure created

EXECUTE PROCEDURE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Execute the above SupplierProducts procedure.
EXEC SupplierProducts
Result:  78 records
CompanyName ProductName UnitPrice Package
Aux joyeux ecclésiastiques Côte de Blaye 263.50 12 - 75 cl bottles
Aux joyeux ecclésiastiques Chartreuse verte 18.00 750 cc per bottle
Bigfoot Breweries Sasquatch Ale 14.00 24 - 12 oz bottles
Bigfoot Breweries Steeleye Stout 18.00 24 - 12 oz bottles
Bigfoot Breweries Laughing Lumberjack Lager 14.00 24 - 12 oz bottles
Cooperativa de Quesos 'Las Cabras' Queso Cabrales 21.00 1 kg pkg.

ALTER PROCEDURE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Change the SupplierProducts procedure to add a Country column.
ALTER PROCEDURE SupplierProducts
AS
 BEGIN
  SELECT CompanyName, ProductName, UnitPrice, 
         Package, Country
    FROM Supplier S
    JOIN Product P ON P.SupplierId = S.Id
   ORDER BY CompanyName
 END 
Result:  Procedure altered

DROP PROCEDURE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Remove the SupplierProducts stored procedure.
DROP PROCEDURE SupplierProducts
Result:  Procedure dropped

RENAME PROCEDURE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Rename the SupplierProducts stored procedure to ProductsBySupplier.
DROP PROCEDURE SupplierProducts

CREATE PROCEDURE ProductsBySupplier
AS
 BEGIN
  SELECT CompanyName, ProductName, UnitPrice, Package
    FROM Supplier S
    JOIN Product P ON P.SupplierId = S.Id
   ORDER BY CompanyName
 END 
Result:  Procedure dropped; procedure created

Note: Renaming a stored procedure is not available in SQL Server. To rename, drop the procedure and then re-create it with a new name.


T-SQL and Stored Procedures

T-SQL, or Transact-SQL, is an extension to SQL that is available in SQL Server.

It's a basic programming language with variables, flow-control, try-catch, and more.

T-SQL is often used in stored procedures.

Below is a simple T-SQL stored procedure with an if statement.

CREATE PROCEDURE ListCustomers
  @top INT,
  @full BIT
AS
 BEGIN
  IF (@full = 1)
  BEGIN
   SELECT TOP (@top) *
     FROM Customer
  END
  ELSE
  BEGIN
    SELECT TOP (@top) FirstName, LastName
     FROM Customer
  END
END


To execute the above procedure, use EXECUTE with two parameters, like so:
(true is converted to 1).

EXECUTE ListCustomers 5, true
Result:  5 records
Id FirstName LastName City Country Phone
1 Maria Anders Berlin Germany 030-0074321
2 Ana Trujillo México D.F. Mexico (5) 555-4729
3 Antonio Moreno México D.F. Mexico (5) 555-3932
4 Thomas Hardy London UK (171) 555-7788
5 Christina Berglund Luleå Sweden 0921-12 34 65

List All Stored Procedures

This is one way to list all user-generated stored procedures:

SELECT * 
  FROM sys.procedures

And another way.

SELECT * 
  FROM INFORMATION_SCHEMA.ROUTINES  
 WHERE ROUTINE_TYPE = 'PROCEDURE'

Benefits of Stored Procedures

Efficiency. When executed the first time, they creates a plan that is stored in cache.

Reusability. Stored procedures can be used multiple times.

Reduced network traffic. Only the procedure name is passed.

Security. It may reduce threats by removing direct access to database objects.

Flexibility. Stored procedures are easily modified at runtime.


Disadvantages of Stored Procedure

Stored procedures can be difficult to debug.

Requires skilled personnel with T-SQL experience.

Only works on SQL Server. Other database vendors support different languages.


Stored Procedures vs Functions

A list of differences between stored procedures and functions.

Stored Procedure Function
Return value is optional Must return a value
Supports input and output parameters Only supports input parameters
Cannot be called from a function Can be called from a procedure
Allows SELECT, INSERT, UPDATE, and DELETE queries. Only allows SELECT statements
Cannot be utilized in a SELECT statement Can be embedded in a SELECT statement
Supports Try-Catch exceptions Does not support error exception
Supports Transactions Does not support Transactions

System Stored Procedures

Each SQL Server installation comes with many built-in system procedures.

These procedures are stored in the 'master' database.

Many system procedures are named with an sp_ prefix (for stored procedure).

They prevent users from modifying system and database catalogs directly.

Developers often ignore these procedures, but they are important for administrators.

These are some of the commonly used system procedures.

Procedure Description
sp_monitor Provides runtime statistics about the SQL instance.
sp_help Provides information about database objects
sp_helptext Returns the definition (DDL) of a database object
sp_who2 Provides information about current users, sessions, and processes.
sp_kill Kills a session. Useful in deadlock situations.
sp_tables Returns information about tables and views
sp_depends Returns the dependencies of a database object
sp_executesql Used in T-SQL to execute dynamic SQL. Be mindful of SQL injection.
sp_getapplock Identifies transaction and object locks.

You may also like



Guides


vsn 3.1