Global variables and Functions
Does the concept of global variables exist in SQL-Server 2019?
In SQL Server 2019, the concept of global variables does exist, but it takes a somewhat different form than what you may be familiar with from procedural programming languages.
In SQL Server, global variables are system-defined special type of variables that provide information about the database or the system, depending upon where they are used. They start with two at signs (@@) instead of one. These variables represent the current system information pertaining to SQL Server. They are read-only and self-maintained by SQL Server.
For instance, the global variable @@VERSION returns the current version of SQL Server, including the version, processor architecture, build date, and operating system. Another common global variable, @@ROWCOUNT, returns the number of rows affected by the last executed statement.
Here's an example of usage:
SELECT @@VERSION AS 'SQL Server Version';
However, it's important to note that you cannot declare your own global variables in SQL Server 2019, nor can you modify the values of these system global variables. The system automatically sets and maintains them. They are predefined and exist for every connection to SQL Server.
If you're looking for a way to create variables that can hold values across multiple batches or stored procedures, SQL Server does not inherently support this. But, there are ways around this, using either the CONTEXT_INFO function or a more persistent storage medium like a temporary table or a table variable, though these methods have their own constraints and limitations.
Therefore, while SQL Server 2019 does support the concept of global variables, it's important to understand the specifics of how these variables function in the context of SQL Server, and to keep in mind that they may not operate as "global variables" might in other programming contexts.
In SQL Server versions prior to 7.0, there was a concept known as global variables.
Global variables stored system-level information such as
@@error, which returns the error of the last executed Transact-SQL statement.
These concepts still apply to version 7.0, but they are now referred to as functions, not as global variables.
One of the benefits of the SQL Server 2012 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL.
This means you can use the global variable as an input parameter for your stored procedure. Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call your stored
procedure, using a question mark as the placeholder for your parameter:
EXEC dbo.spInputTest ?
Once you have entered the SQL statement you can click Parameters and you will be presented with the Input Parameters tab.
The mapping list view will have one row for each placeholder found in the SQL. There parameters have no meaningful names, they are listed in order, so Parameter 1 will be the first meaningful found, Parameter 2 will be the second placeholder found on so on.
For each parameter found you can select a global variable to map to that parameter. At run time the provider (ODBC or OLE-DB) parameter support will be invoked and the mapped global variable value will be substituted for the placeholder.
It is important to note that the parameter handling uses the provider parameter support since not all providers support parameters, and others provide only limited or unreliable support. Obviously the Microsoft SQL Server providers have full support. Another important point about parameters is that just like when using local variables in SQL they cannot be used to parameterise object names such as procedure names, table names or column names. In a normal SQL statement you can workaround this by using dynamic SQL, but in DTS you have the option of dynamically building the SQL statement