2

I'd like to have something similar to a C++ integer constant that I could use across different stored T-SQL procedures:

SELECT * FROM SOMETABLE WHERE STATE = IsBeingProcessed;

with IsBeingProcessed being a named integer constant equal to say 4.

Is it possible in T-SQL?

sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • 2
    There are several SO posts tackling constants in SQL - e.g. http://stackoverflow.com/questions/3370737/best-pattern-for-state-constants-in-sql-server-dbproj – StuartLC Oct 17 '11 at 08:38

2 Answers2

3

You could create a config table with id, name and value populated with 'BeingProcessed' and 4 and join to the table. Would also foreign key it if possible. This also allows for the status definition to be updated as a table update. i.e. Business decide to change the name from being processed to awaiting processing.

Andrew Jansen
  • 186
  • 1
  • 5
  • +1 this is the right answer and the most accepted way to store application/global configuration. –  Oct 18 '11 at 11:37
0

You could create a User Defined Function in the master table which simply does the following:

CREATE FUNCTION dbo.IsBeingProcessed
(
)
RETURNS int
AS
BEGIN
    RETURN 4
END

Then this could be called like:

SELECT * FROM SOMETABLE WHERE STATE = dbo.IsBeingProcessed();
Curtis
  • 101,612
  • 66
  • 270
  • 352
  • 2
    This does have the disadvantage that SQL Server won't "sniff" the UDF value in order to determine more accurate cardinality estimates. – Martin Smith Oct 17 '11 at 08:51