Originally posted
here.
Tony Rogerson
brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT.
The issue? With SELECT you can assign values to multiple variables
simultaneously. But with SET, you can set up your assignment such that
you get an exception if more than one row is assigned to the variable.
Both are desirable qualities... But unfortunately, as Tony shows us,
it's difficult to achieve both multiple assignment and getting the
exception thrown, at the same time. Tony shows us a solution involving
checking for the number of rows affected after the assignment. Creative
and effective, but it still has an issue: Unlike with SET when it
throws an exception, with Tony's solution the variables will still have
been affected by the assignment.
As I was reading Tony's post, I
couldn't help but think that there must be another way. And low and
behold, there is -- at least, in SQL Server 2005. Thanks to the power
of windowed aggregates we can have our multiple pieces of cake and eat
them, all at the same time. Wonderful stuff.
So, here's what you
do: Set up a CTE that selects the columns you'd like to assign to your
variables, and also get COUNT(*), partitioned by 1 (or some other
arbitrary literal). By partitioning by a literal, we will end up with
the row count for the entire set. In the outer query, express the
assignments from the columns returned by the CTE, but add an additional
WHERE clause that compares the value of the COUNT(*) column with a
subquery against a table of numbers. In the following example which
I've adapted from Tony's blog, I'm using master..spt_values for the
numbers, but you are encouraged to use a properly-indexed table of
numbers, should you decide to use this technique:
DECLARE
@reserved INT,
@rowcnt INT,
@used INT
SET @reserved = -1
SET @rowcnt = -1
SET @used = -1
;WITH x AS
(
SELECT
reserved,
rowcnt,
used,
COUNT(*) OVER(PARTITION BY 1) AS theCount
FROM sysobjects so
INNER JOIN sysindexes si ON si.id = so.id
WHERE
so.name = 'sysrowsets'
)
SELECT
@reserved = reserved,
@rowcnt = rowcnt,
@used = used
FROM x
WHERE theCount =
(
SELECT
number
FROM master..spt_values
WHERE
TYPE = 'p'
AND number BETWEEN 1 AND theCount
)
SELECT @reserved, @rowcnt, @used
As
you'll see if you run this on your end, an exception is thrown and the
values of the variables are not affected. This works because the
subquery used in the WHERE clause will return more than one value if
theCount is greater than 1, thereby violating the rule that subqueries
must only return one value.
The price you'll pay for this
convenience? Extremely complex code for a simple variable assignment,
in addition to a slight performance penalty. Is it worth it? Probably
not, at least for me. To be honest, I seriously doubt I will ever use
this -- I've never been especially concerned with the chance of
multiple rows screwing up my variable assignment, and those times that
it has happened, I've remedied the situation other ways (e.g., defining
a better primary key). That said, I think this was an interesting T-SQL
challenge, and if anyone comes up with a more elegant solution than
Tony's or mine, I'd love to see it!