The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Constants or enums in sql server stored procedures

Is there a way to define constants or enums in sqlserver stored procedures?

I want to define constants or enums in one place so it is more managable and understandable rather than hard coding numbers everywhere.
Thursday, August 28, 2008
1) Best:
Store the values in the database and use them as appropriate.

2) Worst:
Store the values in the database and 'exec' your calculated dynamic SQL.

I would prefer hard-coded values to (2).
Thursday, August 28, 2008
If you mean T-SQL stored procedures, you are out of luck, but it is trivial in managed stored procedures (C#, etc).

I remember the pain of making pseudo-constants in T-SQL for a gig I did at an i-bank:

declare @constExample int

select @constExample = 123


select ID, Name
from Example
where Category = @constExample

It was a convention that worked, dreamed up by one of the i-bank's bright graduates, but it was really painful.
Scorpio Dragon Send private email
Thursday, August 28, 2008
At my last place, I did what Scorpio Dragon was showing. Because the existing stored procs had lots of constants, and this 4 over here wasn't the same 4 as that other 4 in the same stored proc.
Peter Send private email
Thursday, August 28, 2008
You can use a function to return the constants - then they would be all in one place.
DJ Send private email
Thursday, August 28, 2008
Yeah, as DJ says, for commonly used constants, functions are good, and I have used them too.

It becomes messy though, if you have lots of constants in your application.
Scorpio Dragon Send private email
Friday, August 29, 2008
Or a compromise: store them in a db table but fetch them with scalar functions.

WHERE OrderType = dbo.f_GETCONST('ConstName')

That gets you all the constants in one place plus a table, at the expense of making your calls require more testing.
Brian Send private email
Friday, September 05, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz