How do I save a stored procedure in SQL Server 2008 R2?

Question

I am writing a SQL Server stored procedure for the first time and am unclear on how I can "save" my stored procedure so that it appears under Programmability, Stored Procedures in the Object tree.

Answer 1

The CREATE PROCEDURE procedureName statement creates the procedure.

You just need to execute it once and it will save the procedure to your database.

Make sure to select the correct database you want to save the procedure to, either by selecting it in the top left hand corner of SQL Server Management Studio, or by putting the following at the top of your code:

USE databaseName

Also note, if there are any syntax errors, it won't "save" the procedure.

Answer 2

While you are learning SQL Server and Management Studio, you may find it very helpful to become familiar with the built-in templates for creating everything from databases to tables to stored procedures and more. You locate the templates in Template Explorer under the View menu.

The first example in this walk-through with screenshots shows how to use the template for creating a stored procedure. That template includes a placeholder for the schema name (often just dbo).

You will also want to include a USE statement to make sure that the stored procedure is created in the correct database.

In addition to helping you to learn proper coding practice, using these templates can be a real time-saver and help you to avoid typos and syntax errors even after you becomem proficient in SQL.

And when you get really good at it, you can create your own templates.

Edit: Here is a very basic CREATE PROCEDURE statement:

USE MyDatabase
GO
CREATE PROCEDURE dbo.MyProcedure
AS
SELECT FirstName, LastName, Address, City
FROM Customers
ORDER BY LastName
GO

After you run that, you can run this line to check that the procedure has been created and that it is working correctly:

EXEC dbo.MyProcedure