title: What is the best way to define a named constant in SQL?
body: <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 &lt;= <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="noreferrer">@@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() &lt; @@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() &lt; 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() &lt; (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() &lt; dbo.EventSeverity()
          ...
      ELSE
          ...
  END CATCH
  </code></pre></li>
  </ul></li>
  </ul>
  
  <p>Is there a best practice way to do this?</p>