W3Schools SQL | #34 SQL STORED PROCEDURES - SQL SERVER

SQL Stored Procedures for SQL Server

What is a Stored Procedure?

반복해서 사용할 수 있는 프로세저

Stored Procedure Syntax

• procedure 생성

  CREATE PROCEDURE procedure_name
  AS
  sql_statement
  GO;

• procedure 실행

  EXEC procedure_name;

Stored Procedure Example

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

EXEC SelectAllCustomers;

Stored Procedure with One Parameter

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

EXEC SelectAllCustomers @City = 'London';

Stored Procedure with Multiple Parameters

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';