Back to Basics: Why not parameterize?

I think sometimes those of us that have been doing database administration/development for a while take it for granted that everyone knows the basics. One such basic is parameterizing stored procedures. This allows us to potentially consolidate multiple stored procedures into a single procedure.  It’s as simple thing to do that many don’t.

I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it be a sort, a where clause, or even just an extra field or two that makes it different, some developers think you need a different procedure for each one . Why not consolidate and parameterize?

Exhibit A

The code below is an example of a real work scenario.  Originally, it was 8 stored procedures and with 8 correlated reports. By simply adding a Report Type parameter I was able to make it one stored procedure and as well as consolidate to a single report.

To add a new dataset just right click on Datasets and choose Add Dataset. Since the report is a stored procedure we set the dataset connection string to the stored procedure name and its parameters. This is just my preferred method. You can also choose the stored procedure from the drop down.

rep

rptTrackMonthlyStats @ReportType, @year, @startdate, @enddate

rp

In the Report Type parameter, choose add Available Values. I typed in each option so the user could choose which report layout/data they wanted to see from drop down. That parameter will be passed to the stored procedure upon execution and the proper dataset will be returned. The users will never see the T, TD etc. they only see the label so it doesn’t make any difference to them what those are.

Parareport connectiom

You can even go as far as using these parameters to hide and show different report elements, but that’s for another time. Stay tuned for more back to the basics.

NOTE: There are some reasons not to do this, like the reuse of the execution plans and parameter sniffing but in these cases consolidating would not be an issue as they use the same parameters.

QUICK & DIRTY: Table name change for all stored procedures

Recently, I was tasked to find all stored procedures in a database that use a particular table and change them to utilize another table. I needed to get this done quickly so I decided to think simple. Since it was a quick and dirty answer I figured I would share, it’s not rocket science, but useful none the less.

The quick answer that I came up with is to script out all of the stored procedures into a single query window.  This can be done easily through the GUI.  Once that is complete, I can easily do a “Find & Replace” on the table name and we’re done!

Let me show you how!

Step 1: Script out all stored procedures for a database

Right Click on Database > Choose Tasks> Choose Generate Scripts

Generatescrpts

If you have not used Generate Scripts before it has a great wizard to walk you through it. You can create scripts for the entire database or just certain objects. You can even narrow it down to a single stored procedure. In this case I wanted all stored procedures.

scripts1

Step 2: Choose Stored Procedures all or specific ones

scripts2

Step 3:

Then I went to the ADVANCE Options and set Continue on Error to TRUE so it will  skip any issues that might surface. If there are issues, I will address them at a later time. I also chose to create the scripts to a new query window.  By doing this, it’ll be very easy to do a find & replace on the table names.

scripts3scripts4

Step 4: Once the new query window was populated I did a simple find and replace of the table name I wanted to change. I also changed the word CREATE PROCEDURE to ALTER PROCEDURE for all of the procedures so that I would not need to drop and replace.

Step 5: Back up your database!!! Another option is to simply keep a copy of the script before any modifications had been made. In the event of an error, I can just rerun the original script to put things back to what they were.

Step 6: Run Script

In this case, 365 stored procedures were updated and I had the process completed in about 15 minutes. Not too shabby.

*Remember it’s best to always do this in test first.

So let’s talk naming conventions

How many of you have come across a database that had stored procedures, views or functions and you had no clue, by name, what they were for? Having standard naming conventions helps to prevent that. Everyone has their own preferences and opinions on what they should be, so I thought I’d share mine.

My opinion

In a nutshell, the name of any object should be informative; specifically what the object is used for and where it is used. This is accomplished by utilizing prefixes in conjunction with specific naming conventions.  I apply these standards to all of my stored procedures, views, and functions, whenever possible. Of course there are always times where naming conventions don’t fit the situation, you have to be flexible and logically choose a name that fits that scenario.

Here Are My Standards

standards

Stored Procedures

  • Stored Procedure name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Use Camel Case Only example “rptTheNewStoredProcedure”
  • The following prefixes should be used for all names
  • New Prefixes can be added as needed when a deemed applicable
Prefix Use
rpt Reporting Services Report
app Applications
web Website Exclusively
xls Direct Excel Data Pull
ssis Store Procedure only used in SSIS packages
job SQL Agent Job Step
cube Analysis Services Cube Data

If a stored procedure has multiple uses, I choose the highest entry in the usage hierarchy as the prefix. For example, a job that calls a stored procedure for an application. The job is what calls it, so it would be named with the job prefix. Using the prefix job will point me to SQL Agent. There I should see a job having a job step with same name as stored procedure minus the prefix and a stored procedure of the same name prefixed with job. At any point in it should give the DBA a trail to follow. Don’t duplicate a stored procedure to use in multiple places. Otherwise, any change will have to be done multiple times.

Views

  • View name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Use Camel Case only example “rptVwTheNewView”
Prefix Use
rptVw Reporting Services Reports
appVw Applications
webVw Website Exclusively
jobVw Job Related
cubeVw Analysis Services Cube

I add Vw just so I can differentiate it from a stored procedure or table. Now you may question why I add Vw to views but not p or proc prefix to stored procedures. It’s simply because for me stored procedures are the default and doesn’t need it.

Functions

  • Function name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Prefix with fx
  • Use Camel Case only example “fxTheNewFunction”

Because functions can be called by lots of objects I tend only to use fx and not add rpt etc. Functions are the only objects that deviate from the norm for me.

Right Way

The best use example I can give for how I try to implement naming conventions as a way to identify its use is a stored procedure for a reporting services report. The stored procedure should be named exactly what the report is or will be named, but will have the prefix of rpt. Whoever comes into the database should look at the name and know it’s;

  1. A stored procedure called by a report
  2. There will be an .rdl file that has the exact same name minus the prefix.

Exhibit A

Stored Procedure: rptCostofManufacturedItems

Report Services Report: CostofManufacturedItems.rdl

The Not So Right Way

wrong

The not so correct way

Like the toilet paper roll being put on the wrong way. It is a huge pet peeve of mine to see stored procedures named something that you cannot tell what it is. I get irritated when people prefix a stored procedure it p_ or proc, it’s just a thing for me.  I also despise the wretched underscore and mismatched casing. As you can probably tell, I am particularly persnickety when it comes to naming conventions.

Exhibit B (a real exhibit I have encountered)

Stored Procedure: p_DatabaseA_Skus_customers_ROLLING_vj_v5

Report Services Report: customer items.rdl
The name, as I figured out, was “p” for procedure, database name (which makes no sense since I can find it under that database), then procedure use, the developers initials, and finally a version number.  As you can see the report that is it associated with, tells you in absolutely no way shape or form, that it uses that procedure. Notice the “v5” in the name.  Yep, there were 5 of these puppies.  I could not tell without locating the report which one was actually used. Finding which report was a task in and of its self since the report was named completely different from the stored procedure. With naming conventions like this you waste time just trying to figure out what goes with what.

What’s your standard?

Everyone’s environment is different and some may not agree with my opinion, but it works for me. I’d be interested to hear other standards people use. Maybe I can pick up an idea or two.

The key to this is to come up with standards of your own and implement them.  Use them not only for the objects I listed but also for  triggers, indexes, jobs, and replication. It’s less important WHAT your conventions are. It’s more important that you have them. Pick what works for your company’s business model and push all those who deploy to production, including report writers, to use the same naming conventions.  Cleanly named objects make for happy DBA’s.