Create global declarations

Question

I have multiple stored procedures that use the same declarations and the values are constant throughout.

Here is a sample of re-used declarations:

DECLARE @_status_PO_Auto_Approved as int
DECLARE @_status_PO_Invoice_Exceeds_PO_Amount as int
DECLARE @_status_PO_Item_Code_Mismatch as int

SET @_status_PO_Auto_Approved = 2
SET @_status_PO_Invoice_Exceeds_PO_Amount = 15
SET @_status_PO_Item_Code_Mismatch = 16

How can I make these declarations global, so I can use them in different procedures?

Answer 1

You can't. You could do one of a few things though:

• Move them into scalar functions

CREATE FUNCTION dbo.fn_status_PO_Auto_Approved ...

• Write a SQLCMD script to generate those values with a script wide variable.

• My personal preference, create a config table with two (or more) columns, i.e.

VariableName                          Value
_status_PO_Auto_Approved              2
_status_PO_Invoice_Exceeds_PO_Amount  15

etc. You could then add constraints and whatnot to ensure that values are unique and/or restricted in whatever ways you need.

Answer 2

If the values are used only in queries, you could also declare them as a view:

CREATE VIEW dbo._status_PO
AS
SELECT
  Auto_Approved             = 2,
  Invoice_Exceeds_PO_Amount = 15,
  Item_Code_Mismatch        = 16
;

and CROSS JOIN that view in your queries to use the values:

SELECT
  ...
FROM
  ...
  CROSS JOIN dbo._status_PO
WHERE
  ... = _status_PO.Auto_Approved
;