如果在数据库中的多个存储过程和函数中需要使用“常量”值,是否有标准方法在一个地方定义它以便于随时使用?
例如,假设我在
我可以设置一个常数
我考虑了以下可能性:
例如,假设我在
CATCH
块中使用xp_logevent
在事件日志中写入一些信息,当RAISERROR
发生时,但是我希望根据 RAISERROR
严重性将其分组为信息、警告和错误。我可以设置一个常数
EventSeverity
使得:
- 如果
RAISERROR
严重性= 0,则xp_logevent
为信息。 - 如果
RAISERROR
严重性≤EventSeverity
,则xp_logevent
为警告。 - 如果
RAISERROR
严重性>EventSeverity
,则xp_logevent
为错误。
我考虑了以下可能性:
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