Create a global static variable in SQL Server?

Question

Is there a way to create a global variable in SQL Server, in such a way that it persists even if the server is restarted, so I can use it in functions?

Example from what I need:

DECLARE @@DefaultValue bit

This variable should never be deleted unless I explicityl do so.

Shimmy Weitzhandler · Accepted Answer · 2009-12-08T04:04:42.703

10

I guess this will work the best for me:

CREATE FUNCTION [dbo].[fn_GetDefaultPercent]()
RETURNS decimal(5,4)
AS
BEGIN
    RETURN 1.0000
END

edited Dec 08 '09 at 04:04

answered Dec 03 '09 at 08:46

Shimmy Weitzhandler

101,809
122
424
632

1

How is this variable? – CompanyDroneFromSector7G Aug 05 '15 at 10:44
@CompanyDroneFromSector7G: You can use it _variable-esque_, like in conditionals, even though it’s merely a work-around for a non-existing feature. – dakab Oct 25 '18 at 12:10
@dakab A constant then, which I guess could go part way to a solution. – CompanyDroneFromSector7G Oct 25 '18 at 12:37

score 8 · Answer 2 · answered Dec 03 '09 at 08:23

8

You can have a look at something like this

"Global variables" in SQL Server

answered Dec 03 '09 at 08:23

Adriaan Stander

162,879
31
289
284

Global variables are not persistent at all. – Shimmy Weitzhandler Nov 30 '10 at 00:04
1

how would you use those global variables in a query? – Haoest Dec 10 '15 at 17:28
This would be better as user-defined functions than stored procedures, particularly for use in a query, otherwise you have to add and assign a variable for each value used. Also, those prefixes are not a good idea: • https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix • https://dba.stackexchange.com/questions/154251/is-adding-the-tbl-prefix-to-table-names-really-a-problem • https://github.com/red-gate/SQL-code-smells#naming • https://xkcd.com/645/ – brianary Sep 12 '18 at 17:44

score 5 · Answer 3 · answered Dec 03 '09 at 08:22

Not a global variable.

There's chance you can define a global UDF like you can create a "system" stored proc (starts "sp" in master), but I've not tried it.

Note:

Even DECLARE @@DefaultValue bit is actually local:

@ means local variable, identifier is @DefaultValue

It's not really global: try SELECT @@DefaultValue from 2 another query window

score 0 · Answer 4 · answered Apr 24 '14 at 17:38

0

I know is answered but just for fun :)

How about a table with 2 columns like:

GLB_VARIABLES:
GLB_VAR_NAME varchar(100) PRIMARY KEY,
GLB_VAR_VALUE varchar(100)

answered Apr 24 '14 at 17:38

Zberteoc

1

Astander's answer links to an example of how to do this kind of thing robustly, and how to actually use them. – cincodenada Apr 24 '14 at 18:00

Create a global static variable in SQL Server?

4 Answers4

Linked

Related