To create a stored procedure in SQL Server:

  1. Click New Query on the SSMS toolbar
  2. Type (or paste) aCREATE PROCEDURE statement (example below)
  3. Click the Execute button on the toolbar

This will add the stored procedure to the database. You can see it under the Stored Procedures node in the Object Explorer.

Below are screenshots and more detail for the above steps.

New Query

Click New Query in the SSMS toolbar:

Screenshot of the SSMS with the New Query button highlighted (SQL Server 2014)
The SSMS with the New Query button highlighted.

The CREATE PROCEDURE Statement

Type aCREATE PROCEDURE statement.

Example

Here's an example of aCREATE PROCEDURE statement:

CREATE PROCEDURE LatestTasks @Count int AS SET ROWCOUNT @Count SELECT TaskName AS LatestTasks, DateCreated FROM Tasks ORDER BY DateCreated DESC

This script creates a stored procedure called LatestTasks that accepts a parameter called Count.

How to Write a CREATE PROCEDURE Statement

You start your script with CREATE PROCEDURE [procedure_name] AS.

For example, to create a stored procedure called GetCustomer, start with the following:

CREATE PROCEDURE GetCustomer AS

Follow that with the SQL code for the stored procedure – just like in the above example.

If the stored procedure is to accept parameters, add these and their data type to the first line. Prefix the parameter name with the @ symbol.

For example to add a parameter called CustomerId with a datatype of Integer:

CREATE PROCEDURE GetCustomer @CustomerId int AS

Execute the Statement

Click the Execute button on the toolbar to run the SQL statement which creates the stored procedure.

Alternatively, press the F5 key on your keyboard.

You should now see the stored procedure in the Object Explorer.

Screenshot of the Object Explorer with the Stored Procedures node expanded.
Stored procedures appear under the "Stored Procedures" node in the Object Explorer.

Execute the Stored Procedure

To execute, or call, the stored procedure, use either the EXECUTE or the EXEC command (both do the same thing).

So it could be either this:

EXECUTE GetCustomer @CustomerId = 7

or this:

EXEC GetCustomer @CustomerId = 7

The above passes a parameter called CustomerId with a value of 7. Changing this value would process a different customer.

The following screenshot shows the effect of passing a different parameter. Two EXEC statements are run against the same stored procedure, but each one passes a different parameter value:

Screenshot of executing a stored procedure in SQL Server using different parameters.
Two statements executing the same stored procedure, but with a different value for the parameter. In this particular stored procedure, the parameter determines how many records are returned.

Executing a Stored Procedure from the GUI

You can call a stored procedure straight from the Object Explorer in the SSMS GUI.

Simply right-click on the stored procedure and select Execute Stored Procedure...:

Screenshot of executing a stored procedure from the Object Explorer in SQL Server 2014.
Selecting the "Execute Stored Procedure" option from within the Object Explorer.

This results in the following "Execute Procedure" dialog appearing, where you can provide values for any parameters that the stored procedure will use:

Screenshot of the Execute Procedure screen in SQL Server 2014.
The "Execute Procedure" screen. You can provide the value of any parameters at this screen.