Enough of the Hand Rolling – Easy Stored Procedures for your SELECTs

The problem with the I.T. industry in general, is everyone still wants to hand roll.

Here is a handy helper block supplied by Microsoft to help you create stored procedures for your tables.   The only requirement is you need to create the helper functions and procedures in the same database as your tables and your tables must have a primary key.  The following article works through how to use this helper code to create SELECT stored procedures for your tables.

For a start you will need to download and install the helper functions and stored procedures from MSDN Hand Roll Quit Kit.  Be sure to create the functions first, followed by the stored procedures.

Now, to generate a SELECT stored procedure for your table as output but not execute (my sample table is called “FactError”) …

EXEC pr__SYS_MakeSelectRecordProc 'FactError', 0

…and to output the script code but create the stored procedure in the same go …

EXEC pr__SYS_MakeSelectRecordProc 'FactError', 1

The output of the script for my FactError demo table produced the following code.  You can then go and modify the procedure as required to pull back any additional columns and joins, like your dimension tables.

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'prApp_FactError_Select')

       DROP PROC prApp_FactError_Select

GO

----------------------------------------------------------------------------

-- Select a single record from FactError

----------------------------------------------------------------------------

CREATE PROC prApp_FactError_Select

       @FactErrorId int,

       @Error_Id int,

       @Date_Id int,

       @Time_Id int,

       @Process_Id int,

       @Server_Id int

AS

SELECT FactErrorId,

       Error_Id,

       Date_Id,

       Time_Id,

       Process_Id,

       Server_Id,

       ErrorCount

FROM   FactError

WHERE FactErrorId = @FactErrorId

 AND   Error_Id = @Error_Id

 AND   Date_Id = @Date_Id

 AND   Time_Id = @Time_Id

 AND   Process_Id = @Process_Id

 AND   Server_Id = @Server_Id

GO

As an added bonus the helper procedures and functions can also create UPDATE, INSERT and DELETE procedures however just the SELECT is good enough for the purposes of business intelligence.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s