Recently I received a very good question from one of the developer in my team. She wanted to create user defined variable in a stored procedure and use the same global variable in a user define function.
I know we can create Global variables in DTS packages (SQL Server 2000 or prior) and in SSIS (SQL Server 2005 onwards) and in SSRS (SQL 2012 onwards). I know we can create Global Temporary tables, global temporary stored procedures but I have never heard about the user defined global variables.
I checked the BOL and other stuffs and I was assure to know this
"SQL Server does not allow users to create user defined global variables OR update the value of system defined global variable."
So what is the solution if I have to create a user defined global variable, the solution which I come across during my finding is pretty simple. The solution involves following steps
Step 1: Create a table with two columns (variableName and varaiableValue) .
Create table tblGloablVariable
(
variableName varchar(100),
varaiableValue varchar(100)
)
This table will hold all the Global variables we will create in our database.
Step 2: Create a stored procedure which will create and initialize the user defined global variables in our database.
/*********************************************
Procedure to set the Global Variable
**********************************************/
Create procedure setGloablVariable
@param1 varchar(100),
@param1Value varchar(100) As
BEGIN
IF EXISTS(SELECT * from HumanResource.dbo.tblGloablVariable Where variableName=@param1)
BEGIN
UPDATE HumanResource.dbo.tblGloablVariable SET varaiableValue=@param1Value where variableName=@param1
RETURN -1
END
ELSE
BEGIN
INSERT INTO HumanResource.dbo.tblGloablVariable VALUES(@param1,@param1Value)
RETURN 1
END
END
Step 3: Create a stored procedure which will return the user defined global variables values
/*****************************************
Procedure to get the Global Variable
******************************************/
Create procedure GetGloablVariable
@param1 varchar(100),
@param1Value varchar(100) OUTPUT As
BEGIN
IF EXISTS(SELECT * from HumanResource.dbo.tblGloablVariable Where variableName=@param1)
BEGIN
SELECT @param1Value=varaiableValue from HumanResource.dbo.tblGloablVariable Where variableName=@param1
RETURN 1
END
ELSE
RETURN -1
END
Step 4: We can define as many user defined global variables as we need in the system. I defined and initialized following four variables.
-- Define the Global Variable
EXEC setGloablVariable "empName","Vikash Kumar Singh"
EXEC setGloablVariable "Age","29"
EXEC setGloablVariable "OrderNo","9876229"
EXEC setGloablVariable "ItemName","Pencil"
Step 4: We can return the values of the user defines global variables as and when needed.
Hmm, this solution is not new but a very effective solution if you are in a situation to use user defined Global variables. There are many concerns with this approach – the first thing is that you need to have proper rights and roles on the database which will host your tables, stored procedures. The solution will overwrite your changes if some other user is also trying to initialize the same variable. Although, this solution can be extended or customized to override this behavior. /
Let me know what you think or you can point to a better solution to create a used defined global variable.
