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

This blog has moved! You can find this content at the following new location:

http://dataeducation.com/t-sql-variables-multiple-value-assignment/

Published Wednesday, May 24, 2006 12:57 AM by Adam Machanic

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
New Comments to this post are disabled

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.
Privacy Statement