CODEWITHSUNDEEP
t-sqlvariablessql-server-2012initialization
gotqn
gotqn

Reputation: 43626

T-SQL Init several variables from table's row with single select

I have the following table (let's say that its name is "AnswerTable":

QuestionID Value
100        1
101        2
102        1
103        2
104        1
105        2
...

Then I have several variables:

DECLARE @Answer1 INT
DECLARE @Answer2 INT
....

and I need to retrieve information from the table like this:

SET @Answer1 =(SELECT Value FROM AnswerTable WHERE QuestionID=100)
SET @Answer2 =(SELECT Value FROM AnswerTable WHERE QuestionID=101)
...

And what I want to do is to initialize the variables with only one select from the table, because in my real situation the table is very big and it is joined with other tables and I am concerned that multiple execution of the sub query is not going to be efficient.

Is there an easy way to do this?

Upvotes: 1

Views: 2227

Answers (2)

Kaf
Kaf

Reputation: 33809

In General;

SELECT @Answer1 = col1, @Answer2 = col2, ... FROM yourTable

In Your case;

SELECT @Answer1 = case QuestionID when 100 then Value Else @Answer1 End, 
       @Answer2 = case QuestionID when 101 then Value Else @Answer2 End
FROM AnswerTable
WHERE QuestionID IN  (100,101)

Ex: Sql Fiddle Example

Upvotes: 5

asawyer
asawyer

Reputation: 17808

It's a little awkward but you can use joins to do this.

http://sqlfiddle.com/#!3/e3fe7/4

DECLARE @Answer1 INT
DECLARE @Answer2 INT

SELECT 
  @Answer1 = Question100.Value, 
  @Answer2 = Question101.Value 
FROM 
  AnswerTable AS Question100 
INNER JOIN AnswerTable AS Question101 
  ON Question101.QuestionId = 101 
WHERE Question100.QuestionID=100


select @Answer1, @Answer2

Upvotes: 0

Related Questions