-1
Declare @a varchar(100); 

If @a = select productname from product

The above mentioned query is throwing error as my sql query (select product name from product) is returning multiple values.

Can someone help me with this?

user11497433
  • 25
  • 1
  • 4
  • 2
    Do you mean `IN`? What is the expected behaviour here? We don't have enough information. The error is telling you the problem here; you can't compare a scalar value to a dataset containing multiple rows (nor does it make sense to). – Thom A Jun 26 '19 at 09:23
  • Possible duplicate of [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) – Paolo Mossini Jun 26 '19 at 09:24
  • If you are getting multiple products returned, how do you know which one you want to compare with `@a`? How would *you* choose? – Dan Jun 26 '19 at 09:24
  • if you had 3 products, 'pan','spoon','knife' - which one do you want and what is in @a - before and after? What are you wanting to do? - to stop your error you need to select a single product - e.g. If @a = select productname from product where productname = 'pan' print 'I selected the pan'; – Cato Jun 26 '19 at 09:51

2 Answers2

1

Try something like:

Declare @a varchar(100); 
SELECT @a = 'MyProduct'

Then either

If @a = select TOP 1 productname from product ORDER BY <some field>

OR

If @a IN (select productname from product)

However, how do you know which product(s) to match to; you might need a WHERE clause. Some sample data and desired results would help.

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
Peter Smith
  • 5,528
  • 8
  • 51
  • 77
  • 1
    This is a bit of a guess, if nothing else. I *suspect* the OP is more likely after an `IN` here. Especially as the subquery has no `WHERE`, so the value returned from the table `product` is always going to be the same (until a "lower" value is added). – Thom A Jun 26 '19 at 09:29
  • @Larnu Thanks for the comments, I've update my answer. – Peter Smith Jun 26 '19 at 09:35
  • @Larnu Not sure because it's not clear, it may be a `CASE` expression or a`IIF()` function as well. – Ilyes Jun 26 '19 at 09:52
  • @Sami Could be many things - hard to tell without sample data. Thanks for the additional thoughts. – Peter Smith Jun 26 '19 at 10:08
0

Please note that you need to put the SELECT query inside the parenthesis in this case! Also you should note that your select query, in this case should not return more than one value. So to resolve these you need to write it as:

Declare @a varchar(100); 

If @a = (select TOP 1 productname from product)

However your query logically seems to be invalid and you should rethink about it, for example you need to say that, you are going to check @a with which product? You might need to add some filters to query and/or add ELSE to your if, etc.

You might also need to read the @PeterSmith's answer too(Using IN...)

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62