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"; |
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. |
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"; |
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 |
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):
It will display the following:
Statement
---------------
Execution Scope
(1 rows affected)
Result
----------------------------------------
Chilly
(1 rows affected) |
Statement
---------------
Execution Scope
(1 rows affected)
Result
----------------------------------------
Chilly
(1 rows affected)
As always, I hope this helps those looking for a solution.
Subscribe via RSS