How TO DECLARE GLOBAL Variable in SQL Server?

Question

I had coded value which was used in many stored procedures, so to create a global variable (at database level) and use that in all the stored procedure , if the changes again comes then only need to change the value of that variable will impact in all the logic of Stored Procedure. by global variable i mean : @@Error,@@ROWCOUNT or etc.

please suggest if any other way around.

What are you trying to do? "Global variables" have no meaning for a database. You can store configuration but it would be very weird if the behavior of a stored procedure changed automagically whenever a configuration changed. It's better to pass such configuration as parameters — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 13, 2016 at 12:02
Even if you think the opposite - you do not need any global variables. They are evil, they could kill you :) — Andrey Morozov
– Andrey Morozov, Commented Sep 13, 2016 at 12:05

Damien_The_Unbeliever · Accepted Answer · 2016-09-13 11:59:54Z

4

You can create a table and store values there. Then any SP needing the value can query that table. I'd normally recommend fixed to one row and multiple columns (so that each column, which represents a "global variable" can have an appropriate data type).

E.g.

CREATE TABLE Globals (
   X char(1) not null,
   GlobalVar1 varchar(250) not null,
   GlobalVar2 int not null,
   constraint PK_Globals PRIMARY KEY (X),
   constraint CK_Globals_Single CHECK (X='X')
)

And then insert the row:

INSERT INTO Globals (X,GlobalVar1,GlobalVar2)
VALUES ('X','[email protected]',32)

And then update it thereafter. You can then either read specific column values into a local variable in the stored procedure, or just join to this table, depending on what feels most appropriate.

answered Sep 13, 2016 at 11:59

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

Or use extender properties. Depends on what the OP wants to do - it would be very weird if a stored procedure's behavior changed automagically. Better use stored procedure parameters to pass such configuration, no matter where it's stored

Damien_The_Unbeliever Over a year ago

@PanagiotisKanavos - I've used this previously for "config" style data - where we do need a few magic numbers or values that more than one stored proc needs to care about. It's not the only option but it can work quite well for that sort of data, where maybe it needs to be changed a few times a year.

greesh Over a year ago

Damien_The_Unbeliever how you have done this, can you please share. , we have the same situation where changed occurred in a year.

Collectives™ on Stack Overflow

How TO DECLARE GLOBAL Variable in SQL Server?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related