Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save EitanBlumin/f0b1b2112548207910b8036f06840e1f to your computer and use it in GitHub Desktop.
Save EitanBlumin/f0b1b2112548207910b8036f06840e1f to your computer and use it in GitHub Desktop.
Function and stored procedure to implement Global Variables using Extended Properties
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment