Variable Substitution in MSSQL Command Tasks

<< Click to Display Table of Contents >>

SmartConnect 2017

Variable Substitution in MSSQL Command Tasks

When using user global variables in tasks care should be taken with the naming of the variables. When tasks are run variables are inserted into tasks in the order in which they are processed. They simply replace the name of the variable within the task with the relevant value. However because text is replaced with values, if variable names are similar the task may confuse the substitution.


e.g. Passing parameters to a stored procedure in a MSSQL Command Task

GBL_PARAM1 is set to "ABC"

GBL_PARAM1Detail is set to "DEF"


exec sp_someprocedure 'GBL_PARAM1','GBL_PARAM1Detail'


The result of variable substitution for this command would not have the desired effect, instead it would result in:


exec sp_someprocedure 'ABC','ABCDetail'


This is because the first variable will overwrite GBL_PARAM1 with ABC everywhere in the command. When the second variable looks for GBL_PARAM1Detail it finds nothing to replace as the first variable have over-written it.


To alleviate this issue it is important to always give variables a proper name that states what it is. Do not use variable like VAR or A as they will cause substitution issues and will not achieve the desired result.


Also because user global variables are available across all maps, a poorly named variable can be affecting tasks on another map.