3 Replies - 8658 Views - Last Post: 08 March 2012 - 03:24 PM

#1 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

SELECT multiple values into local variable

Posted 07 March 2012 - 07:18 PM

I'm hoping baavgai or one of the other nabobs can help me out here.

I'm trying to optimise some queries, but I have multi-select parameters within the query, for example:
WHERE BusinessUnit IN (@BusinessUnit)



Now, I thought that the way to SELECT multiple values into a variable was done like this:
DECLARE @BusinessUnit AS int;

SELECT
	@BusinessUnit = BU.ID
FROM LookupTables BU
WHERE BU.AttributeName = 'BusinessUnit'



...but when I run this...
SELECT @BusinessUnit



...I only get a single result. Not sure what the solution is, as I'm having trouble finding a resource via my Google searches :(

EDIT: Yes, there are definitely multiple results for AttributeName = 'BusinessUnit' in LookupTables

This post has been edited by e_i_pi: 07 March 2012 - 07:22 PM


Is This A Good Question/Topic? 0
  • +

Replies To: SELECT multiple values into local variable

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3710
  • View blog
  • Posts: 5,958
  • Joined: 08-June 10

Re: SELECT multiple values into local variable

Posted 07 March 2012 - 07:50 PM

I haven't used MSSQL like this for years, but from what I remember, those types of variables are scalar. They can only hold one value, so each row from the SELECT would just overwrite the value set by the previous row.

You can, however, define variables as tables. Then you can INSERT and SELECT from it just like a normal table.
DECLARE @BusinessUnit AS Table( [id] INT );

INSERT INTO @BusinessUnit([id])
SELECT BU.ID
FROM LookupTables BU
WHERE BU.AttributeName = 'BusinessUnit';


SELECT ...
WHERE BusinessUnit IN (SELECT [id] FROM @BusinessUnit)



Not sure if this is the method you are after, but it should work.
Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: SELECT multiple values into local variable

Posted 07 March 2012 - 08:01 PM

Yeah that makes sense. Every time I run the original script, it populates with the latest value returned from the SELECT, which could be anything.

I was aware that you could do it with table variables, but that involves inherent changes to the actual query that I'm trying to benchmark/optimise. This is not ideal, since I'm not actually benchmarking the query, but a variant of it.

I guess this is the way I'll have to go though, since there doesn't seem to be an alternative :/ I wonder how SSRS handles passing the multi-value parameters over then? It must do some sort of casting/conversion to jam it in.
Was This Post Helpful? 0
  • +
  • -

#4 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: SELECT multiple values into local variable

Posted 08 March 2012 - 03:24 PM

You could also take Atli's approach and do an inner join rather than a sub query:
SELECT ...
FROM [Table] t
INNER JOIN @BusinessUnit bu
ON t.ID = bu.ID



That may (or may not) make a difference on how the query performs. Typically subqueries aren't the best approach, but it all depends on the data. You would have to see in the execution plan...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1