Forked from EitanBlumin/extended properties global variable wrapper functions.sql
Created
March 20, 2022 17:17
-
-
Save brovish/2e0467578ae8de429ecea5b4628b42da to your computer and use it in GitHub Desktop.
Function and stored procedure to implement Global Variables using Extended Properties
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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