Press "Enter" to skip to content

Category: Stored Procedures

Leaving Good Comments in a Stored Procedure

Erik Darling comments on your comments:

Possibly the least helpful, but most humorous, way of leaving comments, is a large block of green text up at the top of a module.

There are all sorts of helpful insights buried in those comments to help me as a consultant understand my audience.


I agree with a lot of where Erik is going with his thoughts. The area where we probably have some daylight is that I’d rather limit comments to statements of why rather than what. Sure, when I’m pseudo-coding out a procedure, I’ll have a bunch of little “do this thing here” types of comments, but I remove those as I build out the code. Instead, explain why you’re doing something if it isn’t patently obvious, if you rewrote a query in a more complicated to read fashion because it performs much better, that kind of thing.

But in fairness, as long as your comments actually reflect the code, it’s really hard to say any code base is ever over-commented. It’s way easier to go the opposite direction.

Leave a Comment

ANSI and SET Options in Stored Procedures

Erik Darling starts a new series:

This aligns my stored procedures with the necessary settings to accomplish a couple things:

  • Allow the optimizer to use indexed views, filtered indexes, and computed columns
  • Avoid errors when modifying tables involved with indexed views, filtered indexes, and computed columns

Click through to see what Erik sets by default. It’s a good list, though a bit more than I think to do. Probably because I haven’t been burned enough yet.

Leave a Comment

Thoughts on Testing Stored Procedures

Erik Darling has a plan:

While most of it isn’t a security concern, though it may be if you’re using Row Level Security, Dynamic Data Masking, or Encrypted Columns, you should try executing it as other users to make sure access is correct.

When I’m writing stored procedures for myself or for clients, here’s what I do.

Click through for Erik’s guidance. The premise is couched in security testing, though much of this is functionality and performance testing Regardless, it’s a good plan.

Comments closed

Debugging SQL Server Stored Procedures

Erik Darling lays out a good opinion:

This can really save your hide when you hit a reproducible issue. Proper error handling is part of proper debugging, too.

I generally detest PowerShell, but I love that some commands have a -WhatIf parameter to test what would happen. For normal SQL Server queries, you don’t get that.

But if you’re writing dynamic SQL that may execute database commands (backup, checkdb, settings changes, etc.), you should also include a parameter that prevents dynamic SQL execution so you can debug without worrying about wreaking havoc.

Read the whole thing. I might handle the specific mechanisms of debugging slightly differently, but Erik’s packed this post full of good advice.

Comments closed

CREATE OR ALTER and sp_ Procedures

Louis Davidson hits an edge case:

It isn’t that such objects are to be completely avoided, it is that they are ONLY to be used when you need the special qualities. Ola Hallengren’s backup solution creates a dbo.sp_BackupServer procedure so you can run the backup command from any database.

But if you don’t need the special properties of the sp_procedure, they are bad for the reasons Aaron stated, the reason I stumbled upon today being just a special subset. In this case CREATE OR ALTER behaves differently than CREATE in a way that was really confusing to me as I was working on a piece of code today.

Read on for Louis’s tale of woe and confusion, but mostly confusion.

Comments closed

Decrypting Stored Procedures with SQL Compare

Steve Jones cracks the case:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.

Read on to see how it works.

Comments closed

Decrypting Stored Procedures in SQL Server

Steve Jones breaks the connection:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Stored procedure encryption is one of the more annoying features in SQL Server. The idea was, if you wanted to prevent end users from reading your code, you could encrypt the procedures. But in order to use the procedures, SQL Server needed to decrypt them and you needed this to work on restored backups, so the decryption keys needed to be available to that SQL Server. The infrastructure is a bit different from how Microsoft eventually landed Transparent Data Encryption, enough so that it turned out breaking these procedures is trivial, as Steve shows.

I didn’t know that SQL Compare did decryption. The couple of times I needed to do this, I had used a standalone tool which was released in the 2005 timeframe, so it’s good to see something still supported which does this.

Comments closed

Don’t Start Stored Procedure Names with sp_

Chad Callihan provides solid advice:

Everyone has an opinion on naming stored procedures. One opinion that can be agreed upon is that starting a stored procedure with “sp_” is not the way to go. Using “sp_” can only do harm. Let’s take a look at why that is.

Read on for Chad’s reasoning.

I don’t like prefixes at all for stored procedures (or tables). It’s a common misunderstanding of Hungarian notation, as you don’t provide any new and relevant information in the object name: of course it’s a stored procedure; I’m using “EXEC” to execute it, so what else could it be?

The exception to a very good rule is something which you intend to put in the master database and execute from any database context. The best example of this is sp_whoisactive.

Comments closed

Dynamic Data Masking and Formatted Text

Ben Johnston attacks masked strings in different formats:

Clearly this is NOT a suggestion for how you might break the text but is far more of an exercise to show you how a bad actor may attempt to look at your data in ways that would generally not cause red flags.

It is especially important to reinforce the sentiment that Dynamic Data Masking less of a security tool to prevent attacks, but more to hide data from general viewing, and as a tool for building applications where the data still is accessible in some scenarios and not others.

Click through for several examples. As I like to say (over and over), dynamic data masking only works until users get access to write arbitrary queries against a system. If they’re accessing data through an app or via stored procedure calls only, then it be a reasonable part of a broader security posture.

Comments closed

Updating Database Mail Settings via SP

Chad Callihan doesn’t have time for the UI:

If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.

Read on to see how the procedure works and what you can do.

Comments closed