Stored Procedures with SQL Server 2005

Recently we've upgraded our MS SQL Server from 2000 to 2005, 64-bit Edition. This was mainly due to the limited compatibility of 2000 with 64-bit versions of Windows. Shortly thereafter we needed to re-write the SQL in some of our reports and optimize them for speed.

The reports were still in Beta and we had not yet set them up with Stored Procedures. Part of that optimization involved converting the SQL for those reports to use Stored Procedures. This entry will help those unfamiliar with Stored Procedures learn some of their benefits, capabilities, and limitations.

You can think of SQL stored procedures as a placeholder for a long (or just long-running) SQL query. So instead of:

SELECT blah.idBlah, blah.helloKitty, blah.otherField FROM blah INNER JOIN
something ON something.idBlah = blah.idBlah WHERE blah.cookie='cake'

you could just have:

EXECUTE usp_blahCookies

Naturally, you must specify what usp_blahCookies means. SQL 2005 makes this easy. In SQL Server Management Studio, under the Object Explorer, expand the database you want to add the Stored Procedure to, expand Programmability, right-click on Stored Procedures and select New Stored Procedures.... Management Studio will generate a nice little template for you to work with, like so:

-- ================================================
-- Template
generated from Template Explorer using:
-- Create Procedure (New
Menu).SQL
--
-- Use the Specify Values for Template Parameters
--
command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
--
This block of comments will not be included in
-- the
definition of the
procedure.
--
================================================
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author:
<author,,name>
-- Create date: <create>
--
Description:
<description,,>
--
=============================================
CREATE PROCEDURE
<procedure_name,>
-- Add the parameters for the
stored
procedure here
<@Param1, sysname, @p1>
<datatype_for_param1,>= <default_value_for_param1,>,
<@Param2, sysname, @p2> <datatype_for_param2,>=
<default_value_for_param2,>
AS
BEGIN
-- SET NOCOUNT ON
added to prevent extra result sets
from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert
statements for procedure
here
SELECT <@Param1, sysname, @p1>,
<@Param2, sysname,
@p2>
END
GO

All you need to do is replace the bracketed fields with what you want, and you're good to go. Here's a quick breakdown of what we have:

<author,,name>, <create>, <description>- Use these fields to document the origin and purpose of this stored procedure
<procedure_name,>- The name of this stored procedure. In our example it would be usp_blahCookies. The usp_ is optional, but a good idea. It stands for User Stored Procedure and helps distinguish this as a user-specified stored procedure as opposed to a system s.p., table, or function.
<@Param1, sysname, @p1> <datatype_for_param1,>= <default_value_for_param1,>,... - Any parameters you wish to pass to your s.p. For example:

An integer parameter called taco:
@taco int

Two integers, and a varchar of the length of 50:
@taco int, @burrito int, @curry varchar(50)

Two integers, the second one having a default value of 5:
@taco int, @burrito int = 5


SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> - The SELECT, INSERT, or UPDATE statement(s) or procedure(s) you wish to execute. This is where our "SELECT blah.idBlah, blah.helloKitty, blah.otherField FROM blah INNER JOIN something ON something.idBlah = blah.idBlah WHERE blah.cookie='cake'" would go.

Stored procedures can greatly increase the speed and performance of your database applications. Perhaps even more importantly, they help to enforce separation of your data layer from your code.

This is by no means an end-all tutorial on Stored Procedures. However, I hope someone out there finds it useful. Here, have some links:

What MS has to say about Stored Procedures in SQL 2000
4 Guys on Stored Procedures
Another reason to use SP's: To prevent SQL injection