Function and stored procedure to implement Global Variables using Extended Properties

extended properties global variable wrapper functions.sql

-- Function to Retrieve a global variable value
-- don't forget to convert to the correct data type
CREATE FUNCTION dbo.global_variable(@VariableName sysname)
RETURNS sql_variant
AS
BEGIN
RETURN (SELECT [value]
	FROM sys.extended_properties
	WHERE major_id = 0 AND minor_id = 0
	AND [name] = @VariableName)
END
GO
-- Stored Procedure to Save a global variable value
CREATE PROCEDURE dbo.global_variable_set
	@VariableName sysname,
	@NewValue sql_variant
AS
SET NOCOUNT ON;
IF NOT EXISTS
(
	SELECT *
	FROM sys.extended_properties
	WHERE major_id = 0 AND minor_id = 0
	AND [name] = @VariableName
)
BEGIN
	EXEC sp_addextendedproperty @name = @VariableName, @value = @NewValue;  
END
ELSE
BEGIN
	EXEC sp_updateextendedproperty @name = @VariableName, @value = @NewValue;
END
GO