I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

"Global variables" in SQL Server

There's no such thing as a global variable in SQL Server.

You can't just do:

DECLARE @@GlobalVar int

You can fake it with CONTEXT_INFO

but i wanted something that would last beyond a session or restart

So i did it like this:

 

USE master
IF OBJECT_ID('dbo.sp_GlobalVariables') IS NOT NULL
    DROP TABLE dbo.sp_GlobalVariables
GO
CREATE TABLE dbo.sp_GlobalVariables
(
    varName NVARCHAR(100) COLLATE Latin1_General_CS_AI,
    varValue SQL_VARIANT
)
GO

IF OBJECT_ID('dbo.sp_GetGlobalVariableValue') IS NOT NULL
    DROP PROC dbo.sp_GetGlobalVariableValue
GO
CREATE PROC dbo.sp_GetGlobalVariableValue
(
    @varName NVARCHAR(100),
    @varValue SQL_VARIANT = NULL OUTPUT 
)
AS
    SET NOCOUNT ON    
    -- set the output parameter
    SELECT    @varValue = varValue 
    FROM    sp_globalVariables 
    WHERE    varName = @varName
    
    -- also return it as a resultset
    SELECT    varName, varValue 
    FROM    sp_globalVariables 
    WHERE    varName = @varName
    SET NOCOUNT OFF
GO

IF OBJECT_ID('dbo.sp_SetGlobalVariableValue') IS NOT NULL
    DROP PROC dbo.sp_SetGlobalVariableValue
GO
CREATE PROC dbo.sp_SetGlobalVariableValue
(
    @varName NVARCHAR(100),
    @varValue SQL_VARIANT,
    @result CHAR(1) = NULL OUTPUT 
)
AS
    SET NOCOUNT ON
    UPDATE    dbo.sp_GlobalVariables 
    SET        varValue = @varValue
    WHERE    varName = @varName;    
    -- if it doesn't exist yet add it
    IF @@rowcount = 0
    BEGIN 
        INSERT INTO dbo.sp_GlobalVariables(varName, varValue)
        SELECT @varName, @varValue
        -- return it as inserted        
        SELECT @result = 'I'
    END 
    -- return it as updated
    SELECT @result = 'U'
    SET NOCOUNT OFF
GO

DECLARE @dt DATETIME
SELECT @dt = GETDATE()
EXEC sp_SetGlobalVariableValue 'GlobalDate', @dt;
EXEC sp_SetGlobalVariableValue 'GlobalInt', 5;
EXEC sp_SetGlobalVariableValue 'GlobalVarchar', 'This is a very good global variable'
EXEC sp_SetGlobalVariableValue 'GlobalBinary', 0x0012314;

GO

EXEC sp_GetGlobalVariableValue 'GlobalDate' 
EXEC sp_GetGlobalVariableValue 'GlobalInt'
EXEC sp_GetGlobalVariableValue 'GlobalVarchar'
EXEC sp_GetGlobalVariableValue 'GlobalBinary'

GO
-- update value in master
EXEC sp_SetGlobalVariableValue 'GlobalVarchar', 'New varchar value'

USE AdventureWorks

EXEC sp_GetGlobalVariableValue 'GlobalDate' 
EXEC sp_GetGlobalVariableValue 'GlobalInt'
EXEC sp_GetGlobalVariableValue 'GlobalVarchar'
EXEC sp_GetGlobalVariableValue 'GlobalBinary'

-- update value in AdventureWorks
EXEC sp_SetGlobalVariableValue 'GlobalInt', 6

EXEC sp_GetGlobalVariableValue 'GlobalDate' 
EXEC sp_GetGlobalVariableValue 'GlobalInt'
EXEC sp_GetGlobalVariableValue 'GlobalVarchar'
EXEC sp_GetGlobalVariableValue 'GlobalBinary'

 

Hope it helps someone.

 

 

kick it on DotNetKicks.com

Print | posted on Monday, April 23, 2007 10:20 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: "Global variables" in SQL Server

FYI You might want to rethink the sp_ ..... name. If MS creates a sp_GlobalVariables object in a service pack yours will be dropped

Denis
4/24/2007 12:36 AM | Denis the SQL Menace
Gravatar

# re: "Global variables" in SQL Server

yes i know that and but it's the risk i'm willing to take :)

what are the odds right?

Any MS people that read this:
Please don't put anything named sp_GlobalVariables in the next release or service pack :)
4/24/2007 9:59 AM | Mladen
Gravatar

# re: "Global variables" in SQL Server

IMHO much better idea is to use scalar-valued function instead of sp_GetGlobalVariableValue procedure. Procedures are not handy enough when you want their result to be processed in server-side logic.
4/24/2007 8:58 PM | Marcin Guzowski
Gravatar

# re: "Global variables" in SQL Server

also it's a bit weird to have a table that starts with "sp_"

also global variables are simply evil in general, in any language. let's hope nobody ever needs it! ;)
4/25/2007 1:30 AM | Jesse
Gravatar

# re: "Global variables" in SQL Server

We name our global variables table SYSVAR IIRC, stands for system variables.

I will never name an object sp_ unless I work for Microsoft. It's against everything I believe in as a DBA. ;-)

I just don't see the advantage of using sp_ even if the object is in the master database. It's just so easy to use the three part naming convention and not break this naming rule.
4/25/2007 4:52 AM | Tara
Gravatar

# re: "Global variables" in SQL Server

jesse;
My next endeavour will be building a singleton pattern with
global variables for sql server :)))
4/25/2007 1:32 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

I can only imagine what Celko would say about a table that is named sp_GlobalVariables ;-)
You did manage to confuse me, I was looking at the code and thought you were inserting into a stored proc, this can never compile was my initial reaction....

Denis
4/25/2007 3:09 PM | Denis the SQL Menace
Gravatar

# re: "Global variables" in SQL Server

well... he'd probably say that i'm a raving lunatic who should be working in the field and that i write complete nonsense

am i anywhere close? :))
4/25/2007 3:16 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

Here I found something by Celko, I changed tbl prefix to sp_ hehehehe, this is just hilarious ;-)


"EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.

This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!


Your code is so awful, you even use the "sp-" prefixes to tell us you
have no idea about RDBMS!


Why do your have "CREATE TABLE dbo.sp_GlobalVariables" when view would work?
Answer: because magnetic tape files have to be materialized


Why do you spit on ISO-11179 rules and use a "sp-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.


You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!!


You need help you cannot get on a newsgroup. "


Denis
4/25/2007 3:45 PM | Denis the SQL Menace
Gravatar

# re: "Global variables" in SQL Server

hehe... i 've seen that on "his" blog. :))
4/25/2007 3:55 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

Don't forget the flamed by Celko pic (http://sqlservercode.blogspot.com/2007/04/flamed-by-celko.html) ;-)

4/25/2007 8:04 PM | Denis the SQL Menace
Gravatar

# re: "Global variables" in SQL Server

nice pic :))
am i the only one who thinks that he look like The Emperor Ming from Flash Gordon??
4/25/2007 9:58 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

More like Anton LaVey http://en.wikipedia.org/wiki/Anton_Szandor_LaVey

;-)
4/26/2007 1:08 PM | Denis the SQL Menace
Gravatar

# re: "Global variables" in SQL Server

LOL!
:))
4/26/2007 1:11 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

While the main discussion touched naming of procedure the more important problem was missed.
The SET procedure should have BEGIN TRAN wrapper around UPDATE/INSERT. Otherwise in high transaction environment there is a high risk to create two variable with same name.
Also it would be good to enforce PRIMARY KEY on the table.
5/14/2007 1:18 AM | Alexander Tarasul
Gravatar

# re: "Global variables" in SQL Server

Can i get the list of global variables in SQL?

www.codepal.co.in
1/9/2008 2:50 PM | Bino
Gravatar

# re: "Global variables" in SQL Server

there are no global variables in sql server.

there are system variables that start with @@ you can find those in
BOL = Books Online = SQL Server help
1/9/2008 2:54 PM | Mladen
Gravatar

# re: "Global variables" in SQL Server

Hi Bino,

You can get a list of internal server variables from here: http://www.stewshack.com/sql/Variables.aspx

laterz.
7/18/2008 2:54 PM | bob crackzor
Gravatar

# re: "Global variables" in SQL Server

Personnally, I used function.
Some useful global variable include the fact that I am on the production or on the test database.

if dbo.OnDebugMode() = 1
begin
set emailOfRecipient = 'me@mycompany.com'
end

I have created a function that returns 1 in my dev DB, 0 in my test DB.
8/6/2008 11:01 AM | GBE
Gravatar

# re: "Global variables" in SQL Server

That looks like a lot of work for something you could simply use Extended properties for. You can use sp_AddExtendedProperty to add an extended property, sp_UpdateExtendedProperty to update it, ::fn_listExtendedProperty to read it and sp_dropextendedproperty to, well, you get the hang of it.. You can probably just have a simple dummy table if you don't have a meaningful table you can tie the variable to in your query.
8/12/2008 5:41 PM | Andrei
Gravatar

# re: "Global variables" in SQL Server

i declared a global variable in one stroed procedure.but i did n't get it in another stroed procedure.how can i get that?
11/28/2008 6:13 AM | angel
Gravatar

# re: "Global variables" in SQL Server

show me the code.
11/28/2008 10:55 AM | Mladen
Gravatar

# re: "Global variables" in SQL Server

I don't know anything about just how bad this code is, and I guess I don't care enough not to use it anyway. I hope that I never know so much that I earn the right to be rude, and I have to say I don't remember ever reading so much bashing over a piece of code.

However, I'll probably rename the table.

I also noticed that for the SetGlobalVariable SP, @result will always be 'U'.

Thanks for your work.
9/9/2009 5:05 PM | PJDiller
Gravatar

# re: "Global variables" in SQL Server

you do realize that this is just to show a concept, right?
there are times when something like this becomes useful.
9/10/2009 10:47 AM | Mladen
Gravatar

# re: "Global variables" in SQL Server

Yes indeed. I'm pleased.
9/10/2009 4:26 PM | PJDiller
Gravatar

# re: "Global variables" in SQL Server

Just use global temporary tables.
Create and Drop it from anywhere when the job is done.

Example:

--## denoting a global temp table
create table ##temp (@Key as int, @Val as varchar(20))

--now you can drop it from anyother procedure if neede in the following way
IF OBJECT_ID (N'tempdb..##temp') IS NOT NULL
Begin
Drop Table ##temp
End
9/28/2009 5:51 PM | Vishal
Gravatar

# re: "Global variables" in SQL Server

Sorry CORRECTION:

Just use global temporary tables.
Create and Drop it from anywhere when the job is done.

--EXAMPLE: -- Copy paste below and test

--Stat with dropping the temp table regardless
IF OBJECT_ID (N'tempdb..##temp') IS NOT NULL
Begin
Drop Table ##temp
End

--## Denoting a global temp table
create table ##temp (KeyVal int, ValDesc varchar(20))
insert into ##temp values(1, 'Val of Global Var')

--You could use it from anyother procedure if needed like the following:
declare @Getint as int , @GetVal as varchar(20)
select @GetVal = ValDesc from ##temp where KeyVal = 1

select * from ##temp

--Now you can drop it from anyother procedure if needed in the following way
IF OBJECT_ID (N'tempdb..##temp') IS NOT NULL
Begin
Drop Table ##temp
End
9/28/2009 6:02 PM | Vishal
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET