Everybody knows that you shouldn’t put lots of business logic into stored procedures, right? Well maybe not, but whether or not you agree with this statement you’ll always find some cases where it just makes things easier or more efficient to have your business rules close to the data. Often business rules mean dealing with record types, flags and other magic numbers. These numbers normally correspond to enumerations or constants in our favourite high level language (for me C#).

So what do we do with all these magic numbers? Of course, we being good programmers, put them into well named variables that makes the code easier to read and the values easier to maintain. But what happens when we have multiple stored procedures using the same values?

I didn’t know the answer to this, so I put a question on Stack Overflow, What are the different ways of handling ‘Enumerations’ in SQL server? I got some good answers, but most did some mapping between VARCHARs and INTs. Hard coded strings are better than hard coded numbers because they are easier to read, but they have other problems such as hard to find typos.

One answer that wasn’t given, and hasn’t been suggested by any other developers I’ve talked to, is the use of scalar functions as constants. I stumbled on this solution today while refactoring some existing functions and ended up with a function that did little more than call another function with a hard coded int value. You can create a simple function that just returns a number:

CREATE FUNCTION COLOR_RED()
RETURNS INT
AS
BEGIN
	RETURN 2
END

This is quite a lot of code for just one constant but it is available to all stored procedures. Maybe they could be generated automatically?

As for performance, I haven’t been able to write a test with any big difference in execution time, but I’m sure there must be some hit. Regardless it is bound to perform better than most of the other answers I got for the initial question.

One problem I have found is that you cannot pass them directly to other stored procedures, you need to introduce an intermediate variable, a bit annoying but not a show stopper.

Maybe you’ve being doing this all the time? If there’s something I’m missing, please leave a comment.

Tags: