- Click New Query on the SSMS toolbar
- Type (or paste) a CREATE PROCEDURE statement (example below)
- 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.
Click New Query in the SSMS toolbar:
The CREATE PROCEDURE Statement
Type a CREATE PROCEDURE statement.
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
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: