How to Create Microsoft SQL Server Stored Procedure

This blog article shows you how to get started with Microsoft SQL Server Stored Procedure. I am achieving this using SSMS. I am using northwind sample database. To create the Stored Procedure, expand the database in my case, Northwind. Expand Programmability, right click on Stored Procedure, select New. select Stored Procedure…

You will see a complex template like the one below.

To create a Stored Procedure to list all the customer records, you only need the following lines.

CREATE PROCEDURE ListCustomers

AS

    SELECT * FROM Customers

GO

The following SQL statement creates a stored procedure that selects Customers from a particular City from the “Customers” table: There is a parameter named @City with nvarchar data type.

CREATE PROCEDURE ListCustomersByCity

@City nvarchar(30)

AS

    SELECT * FROM Customers WHERE City = @City

GO

You can execute the Stored Procedure by using exec then pass in the state like the statement below.

exec ListCustomersByCity @City = ‘Berlin’

The result will be as follows.

One thing you must not do is create a Stored Procedure that is calling another Stored Procedure.

Reference: https://www.w3schools.com/sql/sql_stored_procedures.asp