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.
