SQL Stored Procedures

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

Try it live

---

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

EXECUTE InCountryCustomers 'France'

Try it live

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

---

Syntax to create a stored procedure.

CREATE PROCEDURE procedure_name 
AS
 BEGIN
  sql_statement
 END

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 

---

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 

Try it live

EXEC SupplierProducts

Try it live

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 

Try it live

DROP PROCEDURE SupplierProducts

Try it live

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 

Try it live

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.

---

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

Try it live

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

EXECUTE ListCustomers 5, true

---

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'

---

---

---

A list of differences between stored procedures and functions.

---

These are some of the commonly used system procedures.

---