Reader Quiz - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Reader Quiz

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-29 : 11:56:04

Given a couple of variables: A and B, exchange the values without using temporary variable or storage space.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-29 : 12:13:30

Very oldskool!


DECLARE @a AS int, @b AS int
SET @a = 44
SET @b = 83

SELECT @a, @b

SET @a = @a ^ @b
SET @b = @a ^ @b
SET @a = @a ^ @b

SELECT @a, @b

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-29 : 12:21:54

Yeah, more of a poll I suppose. Hard to get the counts among readers tho....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 13:52:31

This also works

I can't ever remember to use the bitwise operations...


Declare @a int,
	@b int

Set @a = 2
Set @b = 7

Select @a, @b

Select @a = B.a, @b = B.b 
From (Select a=@a, b=@b) A 
Inner Join (Select a=@b, b=@a) B
on A.a = B.b and A.b = B.a

Select @a, @b

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 15:02:43

Nice one Corey!

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 15:18:44

wow -- very cool Arnold ! definitely old-school !

this works for a and b both less than 10000:

DECLARE @a AS int, @b AS int
SET @a = 44
SET @b = 83

SELECT @a, @b

set @b = @b + @a * 10000
set @a = @b - @a * 10000
set @b = @b / 10000

select @A,@B

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 15:26:41

Intereseting solution Jeff.

Corey's solution can also work with non-numeric data though.

[CODE]
Declare @a varchar(10),
@b varchar(10)

Set @a = 'You'
Set @b = 'Me'

Select @a, @b

Select @a = B.a, @b = B.b
From (Select a=@a, b=@b) A
Inner Join (Select a=@b, b=@a) B
on A.a = B.b and A.b = B.a

Select @a, @b

[/CODE]

Duane.

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-29 : 15:27:20

Good idea Corey,

Wouldn't

SELECT @A = A, @B = B 
  FROM (SELECT @A as B, @B as A) X

do the same?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 15:32:28

i don't count that as not using extra store -- you are allocating an entire virtual table or two.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-29 : 15:36:24

quote:
Originally posted by SamC
Wouldn't
SELECT @A = A, @B = B
FROM (SELECT @A as B, @B as A) X

do the same?

Irrespective of whether it should, it doesn't work when I try it -- the query optimizer squishes it down to a constant scan and consequently serializes it as two assignments with no (implied) temporary.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 15:36:33

quote:
Originally posted by SamC

Good idea Corey,

Wouldn't

SELECT @A = A, @B = B
FROM (SELECT @A as B, @B as A) X

do the same?

I thought that as well, but it seems that it interprets that as:
Set @a = @b
Set @b = @a

I am not 'technically' defining an extra store

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-29 : 15:41:59

No, but you're getting it.....

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 15:44:54

These people now days!

All they want to do is Get It

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 15:46:19

sue me...

Corey

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-10-01 : 18:57:50

The best that I can actually get to work is:

DECLARE
   @a		CHAR(1)
,  @b		CHAR(1)

SELECT @a = '1', @b = '2'

SELECT @a, @b

SELECT @a = b.b, @b = a.a
   FROM (SELECT @a AS a) AS a
   CROSS JOIN (SELECT @b AS b) AS b

SELECT @a, @b

You ought to be able to use:

DECLARE
   @a		CHAR(1)
,  @b		CHAR(1)

SELECT @a = '1', @b = '2'

SELECT @a, @b

SELECT @a = @b, @b = @a

SELECT @a, @b

...but it doesn't work according to the SQL-92 specs for deferred assignment (but then we already knew that about MS-SQL)!

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources