>>
>>Dmitry,
>>
>>The best way to create a stored procedure in SQL 2005 is to click on the New Query button (top left) and begin typing. If you absolutely need to use a template, drag one from the template explorer into the content pane and press Ctrl-Shift-M within the new query to bring up the template substitution dialog.
>>
>>The reason that Procedure_name & ProcedureName is in there twice is because it is a placeholder for what you type into the subsitution dialog. The first Procedure_name is the substitution "variable", sysname is the datatype, and ProcedureName is the default value.
>>
>>To execute the stored procedure, add a line like
>>

EXEC Myprocedure 'Hello', 10, '20060901'

to the commented header block. Then after running the entire create script with F5, highlight the EXEC line and hit F5 again. It will run whatever is highlighted.
>
>Keith,
>
>When I follow your suggestion to click on New Query and so on and when I click on Save(after I type the Stored Procedure) I am prompted to save the query in a Projects folder as a .SQL file. Is this how Stored Procedures are saved?
>
>Also, I stopped at a book store at lunch and looked at a book on SQL Server 2005. They have description of how to create a Stored Procedure, but in the book on Right-Click instead of a Template it was showing a different screen when you enter name of the procedure and a few other options. If someone knows how my SQL Server Manager brings a different window, please let me know.
>
>Thank you very much for your help.

Dmitry,

In a perfect world there are 3 components to each database object (including stored procedures):

The least important component is the database object - it is stored on the server in compiled and text formats. It is the object that is opened when you choose Edit on the object browser in SSMS. It can be treated the same as you would treat a .dll file. It can be deleted, renamed, moved, and copied without concern because it can be recreated with the migration script (see below) with a single mouse click. The database object exists only inside of the database.

The second least important component is the migration script. This script is very similar to the text of the database object. It contains the text of the object plus other TSQL code to assist the DBA in creating the database object with the proper execution plan, code to test that the object was created without error, and a way to "uninstall" the object. If the object is a new table, it may include some script that inserts data into the new table. If the object is an index, then the script may include a defrag or compute statistics command.

The migration scripts are like a deployment package in .Net. There is some code in the scripts that would have to be recreated by hand if something happened to the script, but it would not be the end of the world if you had to do it. Migration scripts exist only as text files and the DBA has the ultimate responsibility for them. In practice though, the developer usually creates them and then hands them off to the DBA who takes ownership of the script.

The third and most vital component is the source code. It is what you as the database developer labor to create. It should be treated with care. The source code is full of developer's comments. The source code has comments that detail all of the updates that are made by developers. It should be part of a SSMS project and kept in a source control system. The source code exists only as a text file within the project. It is identical to source code in .Net.

All database objects should come from source code. Even if you design a table interactively, you should generate a CREATE TABLE script and save it in your project immediately. After this, only work through the source code.

It sounds like a lot of extra work but it really isn't. You are doing a few minutes of up front to save hours later on. Once you get the hang of it, you will find that you can still be very productive.

----------
>"click on Save..."

You were prompted to save your work as a text file. To create - a.k.a. "compile" - your source code, run the script (F5) after saving it.

>Also, I stopped at a book store at lunch and looked at a book on SQL Server 2005. They have description of how to create a Stored Procedure, but in the book on Right-Click instead of a Template it was showing a different screen when you enter name of the procedure and a few other options. If someone knows how my SQL Server Manager brings a different window, please let me know.

You may have been looking at an example of a database project in VS.Net 2005. It is similar to an SSMS project but I find that SSMS is a better tool for pure database development because of its tighter integration.