Happy New Year

stored-procedure-in-sql

How to Create a Stored Procedure in SQL

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.

SYNTAX

CREATE PROCEDURE procedure_name
AS
 Sql_statement
GO;

EXAMPLE

CREATE PROCEDURE SelectAllEmployee
AS
  SELECT * FROM Employee
GO;

Stored Procedure with One Parameter

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

Stored Procedure with Multiple Parameter

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

Modifying an existing stored procedure

ALTER PROCEDURE SelectAllEmployee
  @City nvarchar(30)
AS
  SELECT * FROM Employee WHERE City = @City
GO;

Drop Stored Procedure in SQL

DROP PROCEDURE [dbo].[SelectAllEmployee]