Summary of the "SQL Stored Procedures" page on W3Schools:

• Definition: A stored procedure is a prepared SQL code that you can save and reuse multiple times, reducing the need to rewrite queries that are used often.
• Benefits: Stored procedures save time and ensure consistency by reusing logic. They can also accept parameters, allowing queries to be dynamic depending on input values.
• Syntax Example: Basic SQL Server syntax for creating a stored procedure:

  CREATE PROCEDURE procedure_name
  AS
  sql_statement
  GO;
  

  To execute:

  EXEC procedure_name;
  

• Parameterization: Procedures can accept single or multiple parameters (e.g., city, postal code), allowing more flexible and dynamic operations.
• Examples:
  • Creating a procedure to select all customers:

    CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
    EXEC SelectAllCustomers;
    

  • Creating a procedure with parameters:

    CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
    AS SELECT * FROM Customers WHERE City = @City GO;
    EXEC SelectAllCustomers @City = 'London';
    

  • Multiple parameters example:

    CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
    AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO;
    EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
    

• Quiz: The page includes a question confirming that the main purpose of a stored procedure is to save reusable SQL code for repeated use, not for optimizing indexes, creating tables, or handling NULL values.[1]

This resource is a concise introduction covering what SQL stored procedures are, their syntax, parameter handling, and direct examples for SQL Server use cases.

1

SQL Stored Procedures for SQL Server

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

Stored Procedure Example

The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers  
AS  
SELECT * FROM Customers  
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)  
AS  
SELECT * FROM Customers WHERE City = @City  
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers @City = 'London';

Stored Procedure With Multiple Parameters

Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)  
AS  
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode  
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';