Variable Assignment Optimization 1

[MKVAB]

Call me lazy... BUT, Is there any way to make this one assignment statement:

DECLARE @Variable1 int
DECLARE @Variable2 int
DECLARE @Criteria  int
SET     @Criteria = 123

SELECT @Variable1 = (
       SELECT Field1
       FROM   Table
       WHERE  SearchField = @Criteria )

SELECT @Variable2 = (
       SELECT Field2
       FROM   Table
       WHERE  SearchField = @Criteria )

Sure, I could just type out the extra four lines, but it would be so nice to do in all at once!

Thanks!
-MK

 

[MKVAB]

I found a good article:

http://vyaskn.tripod.com/differences_between_set_and_select.htm

It talks about doing this:
SELECT @Variable1 = 1, @Variable2 = 2

But, can you do something like that with a WHERE clause?

Humm....

 

[SQLBill]

Have you tried this:

DECLARE @Variable1 int
DECLARE @Variable2 int
DECLARE @Criteria  int
SET     @Criteria = 123

SELECT @Variable1 = Field 1, @Variable2 = Field2
FROM   Table
WHERE  SearchField = @Criteria )

-SQLBill

 

[SQLBill]

Okay, there was a typo in that. I left a close parenthesis in. Also, you should run a SELECT after all that to actually see the results:

DECLARE @Variable1 int
DECLARE @Variable2 int
DECLARE @Criteria  int
SET     @Criteria = 123

SELECT @Variable1 = Field 1, @Variable2 = Field2
FROM   Table
WHERE  SearchField = @Criteria

SELECT @Variable1, @Variable2

Why the second SELECT? The first acts as a SET and only replaces the values, it doesn't display the result.

-SQLBill

 

[MKVAB]

Yep! That works marvelous! Thank you SQLBill!

The 2nd SELECT, good question! I guess I've just always done it that way. Not anymore!

Thank you!
-MK

 

[SQLBill]

Thanks for the star....by the way, my comment about the second SELECT was in reference to the second SELECT in my solution. It was needed to display the results. The first SELECT in my solution just acts like a SET and updates the variables but it won't display the results.

-SQLBill