MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Variable Scope

without comments

Microsoft SQL Server provides T-SQL with the ability to assign values from the queries to local variables. While T-SQL returns a SELECT-list, there are rules for how you can assign the SELECT-list values. The process is more or less an all or nothing approach when assigning values to a local variable. The rule is quite simple for scalar variables because the SELECT-list may contain multiple values but assignments must be made one at a time. You can’t mix retrieving values with assignments. Lets say you try to write the following block of T-SQL code:

1
2
3
4
5
SELECT 'Execution Scope' AS "Statement";
DECLARE @base NVARCHAR(MAX) = N'';
WITH x AS (SELECT 'Chilly' AS cold, 'Burning up' AS hot)
SELECT @base += cold, hot FROM x;
SELECT @base AS "Result";

You raise the following exception:

Msg 141, Level 15, State 1, Server DESKTOP-4U7EN27, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

You should rewrite the query like this:

1
2
3
4
5
SELECT 'Execution Scope' AS "Statement";
DECLARE @base NVARCHAR(MAX) = N'';
WITH x AS (SELECT 'Chilly' AS cold, 'Burning up' AS hot)
SELECT @base += cold FROM x;
SELECT @base AS "Result";

While the subquery on line 3 returns a multivalued SELECT-list, the assignment statement appends the value associated with the column name or alias, which acts like a key in a dictionary. While a dictionary is a collection of name and value pairs, you can use the name of any dictionary element as a key to return only one value from the dictionary. That is the value that the name identifies in the dictionary.

You can put a GO on line 6 in an interactive session or you can put the five lines into a T-SQL script file and call it from the sqlcmd utility. You can run the script file with the following syntax, assuming you have a student user with student as its password working against items in the studentdb schema:

sqlcmd -Ustudent -Pstudent -dstudentdb -y40 -itestScope.sql -otestScope.out

Effectively, all T-SQL variables are limited to their execution scope. You determine execution scope interactively by providing the GO command. T-SQL script files are bundles that execute in a single execution scope unless you embed the GO command.

Then, you can display the testScope.out file as follows from the command line (in a cmd session):

type testScope.out

It will display the following:

Statement
---------------
Execution Scope
 
(1 rows affected)
Result
----------------------------------------
Chilly
 
(1 rows affected)

As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 15th, 2019 at 10:34 pm