Creating a Stored Procedure in Microsoft SQL Server Management Studio 2005

A stored procedure is a simple way of grouping Transact-SQL statements into blocks that are easily manageable. It's good for this particular example because we want to execute an INSERT statement and have a value returned to us using the SCOPE_IDENTITY() function, which simply returns the last value inserted into the Identity column (in the same scope).

Here's an example table that we'll be working with:



First, open SQL Server Management Studio and connect to the server with the desired database. From here, go to the Object Explorer -> Databases -> (Database Name) -> Programmability. Right click on 'Stored Procedures' and click on 'New Stored Procedure'. A window with a new SQL Query will come up, it should look something like this:



A lot of the commented parts can be ignored. Let's look at the 'CREATE PROCEDURE' section. This will be the name of your stored procedure. I'll name mine: '[dbo].[RssItem_InsertItem]'. Below this is where the Parameters will be entered. It is important to note that the type of the parameter needs to match the type of the column. If there is any confusion, simply check back to the table that you are working with.

Once we've entered all of the parameters for our columns, we need to create the SQL statement that will be executed. In this example, we use an INSERT statement to populate our table with new values. Notice the '@Identity' parameter and the last line of the SQL statement:

SET @Identity = SCOPE_IDENTITY();

This is the function that we'll use to get the value back, in this case, an identifier that will be used when we call this stored procedure.

The final query should look something like this:



Now, all that's left to do is execute the query. If, for some reason, you get errors, check your query and make sure the parameter types match the column types. Also make sure the SQL statement is correctly formatted.

Once you've done all this, you have your stored procedure created and ready for use.