StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat is the best way to define a named constant in SQL?
text
Body
copied!<p>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?</p> <p>For example, suppose I use <code>xp_logevent</code> in <code>CATCH</code> block to write something to the event log when <code>RAISERROR</code> happens, but I want to group the severity into informational, warning, and error based on the <code>RAISERROR</code> severity.</p> <p>I could set a constant <code>EventSeverity</code> such that:</p> <ul> <li>if <code>RAISERROR</code> severity = 0 then <code>xp_logevent</code> is informational.</li> <li>if <code>RAISERROR</code> severity <= <code>EventSeverity</code> than <code>xp_logevent</code> is warning.</li> <li>if <code>RAISERROR</code> severity > <code>EventSeverity</code> than <code>xp_logevent</code> is error.</li> </ul> <p>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.</p> <p>I thought of these possibilities:</p> <ul> <li><p>Use a '<a href="http://msdn.microsoft.com/en-us/library/ms187953.aspx" rel="nofollow">@@variable</a>' to store the value.</p> <ul> <li>Advantages: Low access overhead. Easy to access in code.</li> <li><p>Disadvantages: Imposes execution order, variable must be declared and set before other procedures and functions can access it. Changing value means changing code.</p> <pre><code>DECLARE @@EventSeverity INT = 9 ... BEGIN CATCH IF ERROR_SEVERITY() < @@EventSeverity ... ELSE ... END CATCH </code></pre></li> </ul></li> <li><p>Use a function to return the value.</p> <ul> <li>Advantages: Fairly low access overhead. Easy to access in code.</li> <li><p>Disadvantages: Changing value means changing code.</p> <pre><code>CREATE FUNCTION dbo.EventSeverity() RETURNS INT AS BEGIN RETURN 9 END ... BEGIN CATCH IF ERROR_SEVERITY() < dbo.EventSeverity() ... ELSE ... END CATCH </code></pre></li> </ul></li> <li><p>Use a "settings" table to store the value.</p> <ul> <li>Advantages: Changing value means changing data.</li> <li><p>Disadvantages: High access overhead. Difficult to access in code. Difficult to use as a parameter. User could change value.</p> <pre><code>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 </code></pre></li> </ul></li> <li><p>Use a "settings" table with a function to simplify access.</p> <ul> <li>Advantages: Easy to change the value. Easy to access in code.</li> <li><p>Disadvantages: High overhead. User could change value.</p> <pre><code>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 </code></pre></li> </ul></li> </ul> <p>Is there a best practice way to do this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload