THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

T-SQL Variables: Multiple Value Assignment

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!
 

Published Wednesday, July 12, 2006 10:53 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

Hi Adam,

A similar effect can be obtained (with a more efficient plan and fewer keystrokes!) by using the ROW_NUMBER function in an ORDER BY clause on the assignment, as shown in this example:

-- Table representing some arbitrary result set

DECLARE @T TABLE (id INT NOT NULL)

-- Simulate a query returning more than 1 row

INSERT @T (id)

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3;

-- The variable we will assign to

DECLARE @id INTEGER;

-- Default value

SET @id = -1;

-- The assignment statement

-- The ORDER BY clause will throw an exception

-- if row number 2 exists...

SELECT @id = id

FROM @T

ORDER BY (1 / (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 2));

-- Untouched value if more than one row was returned

SELECT @id;

...

The "ORDER BY (1 / (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 2))" clause can be appended to any assignment statement (the 'select 1' part removes any dependency on the preceding query).

If not saying for a moment that I'll be using this routinely - but thanks for the puzzle!

</Paul>

March 29, 2009 1:32 AM
 

Adam Machanic said:

Good job, Paul!

March 29, 2009 3:45 PM
 

Paul White said:

Thanks Adam!

March 31, 2009 12:33 AM
 

Limojoe said:

what about trick with tinyint (MS SQL 2008)?

DECLARE

   @reserved INT =-1,

   @rowcnt INT=-1,

   @used INT=-1,

   @control TINYINT=0

SELECT

   @control=255*COUNT(*) OVER(PARTITION BY 1) ,

   @reserved =  reserved,

   @rowcnt =  rowcnt,

   @used = used

  FROM sysobjects so

   INNER JOIN sysindexes si ON si.id = so.id

   WHERE

       so.name = 'sysrowsets'

SELECT @reserved, @rowcnt, @used, @control

October 5, 2010 7:02 AM
 

Adam Machanic said:

Nice one, Limojoe!

October 5, 2010 8:33 AM
 

minching said:

This tweaks what has been said above?

DECLARE

   @reserved INT,

   @rowcnt INT,

   @used INT

SELECT

  @reserved =  reserved,

  @rowcnt =  rowcnt,

  @used = used

 FROM sysobjects so

  INNER JOIN sysindexes si ON si.id = so.id

  WHERE

      so.name = 'sysrowsets'

 order by  case when COUNT(*) OVER(PARTITION BY 1) > 1 then cast(cast(COUNT(*) OVER(PARTITION BY 1) as varchar ) + ' Rows returned, expected only one.' as int) end    

-- Should return:

Msg 245, Level 16, State 1, Line 8

Conversion failed when converting the varchar value '3 Rows returned, expected only one.' to data type int.

January 10, 2011 7:54 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement