SQL Server: How to create Stored Procedures

Tweet

"How do I create Stored Procedures?" ... ... ok, I know, this is very basic, but do not feel ashamed if you don't know the answer. When I started playing with SQL Server, Stored Procedures seemed to me a secret and foreign land where everything is difficult and hard to understand. Well, I must say, it is not. So get ready, put on your armor and let's go to slay the dragon (a.k.a. the Stored Procedure)!

What should we use?
We are going to create a Stored Procedure using Ms SQL Server Management Studio. Surprised? Not at all, because - remember - THAT is the tool you should keep under your pillow while you sleep.
Ok! Create a new query and you will have a perfectly blank new query on the right pane of MSSMS.

A simple Stored Procedure
In order to create the Stored Procedure we start stating which db we are going to use. Write the following code:

USE [yourdb]
GO

Replace yourdb with the name of your database.
Now, we give a name to the Stored Procedure:

CREATE PROCEDURE sp_name
AS

Replace sp_name with the name for your new Stored Procedure.
Then we declare a variable and insert the query:

DECLARE @query VARCHAR(8000)

SET @query = 'SELECT * FROM your_table'

Replace your_table with the name of one of yourdb table.
And finally we insert the execution command:

EXECUTE(@query)

You have just created your first Stored Procedure! Well done!
Now... Execute the query by pressing the red exclamation mark on the toolbar.

What happened?

After that - if everything is ok - you will receive a simple statement saying that the query was actually executed and that everything was fine. But then? Where's my Stored Procedure? Confused?
Your wonderful query was actually saved in the relative database. You can find it browsing the left pane tree menu of MSSMS, under the Programmability > Stored Procedures folder of your database.
To execute it, just right-click on the Stored Procedure in the tree menu and select Execute.

Pass variable to Stored Procedures

When you execute a Stored Procedure, you might need to pass a variable. To do so, we need to change the above code as following:

CREATE PROCEDURE sp_name(@firstvar VARCHAR(8), @secondvar VARCHAR(4))
AS

Just after the Stored Procedure name (sp_name), we set two variables (@firstvar and @secondvar) and determine their data type (VARCHAR(8) and VARCHAR(4)).
Those two variables are then used inside your query:

DECLARE @query VARCHAR(8000)

SET @query = 'SELECT * FROM your_table WHERE column1 = ' + @firstvar + ' and column2 = ' + @secondvar

In the example, I used the two variables in the where clause. Column1 and Column2 are columns in your_table.
When writing down the query, remember to use ' in the correct way; in fact you might need to add extra ' to your query if necessary.
If you execute the Stored Procedure with variables, remember to pass values: you can do it by filling the dialog box that MSSMS opens when executing the Stored Procedure itself.

Alter a Stored Procedure

In order to modify a Stored Procedure, right click on its name in the left tree menu of MSSMS and select modify. The Stored Procedure will open and you will see that the "CREATE PROCEDURE" now is "ALTER PROCEDURE". Change the query statement to your needs and execute the query: this will actually modify your Stored Procedure.

This is it! If you need more information, please use the comment section below.