What is the best way to define a named constant in SQL?

Question

If a "constant" value is required in multiple stored procedures and functions in a database, is there a standard way to define it in one place so it is available everywhere?

For example, suppose I use xp_logevent in CATCH block to write something to the event log when RAISERROR happens, but I want to group the severity into informational, warning, and error based on the RAISERROR severity.

I could set a constant EventSeverity such that:

if RAISERROR severity = 0 then xp_logevent is informational.
if RAISERROR severity <= EventSeverity than xp_logevent is warning.
if RAISERROR severity > EventSeverity than xp_logevent is error.

The cut-off between warning and error severity is unlikely to change, but if it ever does I want to change it in one place only.

I thought of these possibilities:

Use a '@@variable' to store the value.
- Advantages: Low access overhead. Easy to access in code.
- Disadvantages: Imposes execution order, variable must be declared and set before other procedures and functions can access it. Changing value means changing code.
```
DECLARE @@EventSeverity INT = 9
...
BEGIN CATCH
    IF ERROR_SEVERITY() < @@EventSeverity
        ...
    ELSE
        ...
END CATCH
```

Use a function to return the value.

Advantages: Fairly low access overhead. Easy to access in code.

Disadvantages: Changing value means changing code.

CREATE FUNCTION dbo.EventSeverity()
RETURNS INT
AS
BEGIN
    RETURN 9
END
...
BEGIN CATCH
    IF ERROR_SEVERITY() < dbo.EventSeverity()
        ...
    ELSE
        ...
END CATCH

Use a "settings" table to store the value.

Advantages: Changing value means changing data.

Disadvantages: High access overhead. Difficult to access in code. Difficult to use as a parameter. User could change value.

CREATE TABLE dbo.Settings
(
    Name VARCHAR(...),
    Value VARCHAR(...)
)
...
INSERT INTO dbo.Settings (Name, Value)
VALUES ('EventSeverity', CAST(9 AS VARCHAR))
...
BEGIN CATCH
    IF ERROR_SEVERITY() < (SELECT CAST(Value AS INT) FROM dbo.Settings WHERE Name = 'EventSeverity')
        ...
    ELSE
        ...
END CATCH

Use a "settings" table with a function to simplify access.

Advantages: Easy to change the value. Easy to access in code.

Disadvantages: High overhead. User could change value.

CREATE TABLE dbo.Settings
(
    Name VARCHAR(...),
    Value VARCHAR(...)
)
...
INSERT INTO dbo.Settings (Name, Value)
VALUES ('EventSeverity', CAST(9 AS VARCHAR))
...
CREATE FUNCTION dbo.EventSeverity()
RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SET @result = (SELECT CAST(Value AS INT) FROM dbo.Settings WHERE Name = 'EventSeverity')
    IF @result IS NULL
        SET @result = 9
    RETURN @result
END
...
BEGIN CATCH
    IF ERROR_SEVERITY() < dbo.EventSeverity()
        ...
    ELSE
        ...
END CATCH

Is there a best practice way to do this?

See also here : http://stackoverflow.com/questions/3370737/best-pattern-for-state-constants-in-sql-server-dbproj — StuartLC, Dec 09 '11 at 07:58
if it's an application specific constant, i would elect to use a dbo.settings approach and ensure that security model on the table prevents modification and thus satisfy the integrity of the constant. — Roman, Dec 09 '11 at 08:07

Lynx Kepler · Accepted Answer · 2011-12-21T10:30:15.113

All else being equal, I would settle for the hard-coded FUNCTION, for performance. For security, this function should be put in a distinct SCHEMA, like MyDatabase.CONF.SettingsFunc instead of the usual DBO; with permissions set to this schema so only the administrators would have permission to change data.

If you need to centralize various configuration settings for many different uses, then the last approach (FUNCTION+TABLE) would have a larger appeal, providing you create an index for each and every use case. Likewise, this "settings" table should be in a restricted schema, unlike the function, which could remain in the default schema for ease of coding.

But, if it is mandatory that the default schema is to be used, it becomes interesting to configure an "INSTEAD OF UPDATE" trigger in this "settings" table, so the user wouldn't change data easily; don't forget that this latter approach can not be called "security" as the user could still change (or drop!) the trigger.

What is the best way to define a named constant in SQL?

1 Answers1