To create a stored procedure in SQL Server:

1. Click New Query on the SSMS toolbar
2. Type (or paste) a CREATE 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:

The CREATE PROCEDURE Statement

Type a CREATE PROCEDURE statement.

Example

Here’s an example of a CREATE 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.

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:

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...:

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