CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlvariables
skmasq
skmasq

Reputation: 4521

Create global declarations

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?

Upvotes: 2

Views: 98

Answers (2)

Andriy M
Andriy M

Reputation: 77707

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
;

Upvotes: 1

Dan Field
Dan Field

Reputation: 21661

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.

Upvotes: 3

Related Questions