how to pass null value in sub query sql server

Question

I am using two sub query. If I pass null or empty value its throwing exception

Exception message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My query

SELECT A.Product_Name AS [Product Name], A.Product_Id AS [Product Id], B.[DuplicateId]

FROM tb_new_product_Name_id AS A, 
(
SELECT COUNT(Product_id)+1 AS [Duplicate Id]
FROM tb_new_product_Name_id_duplicate
WHERE Product_id= (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )
 ) AS B
 WHERE  Product_Name LIKE '%'+@product_name_id+'%' OR Product_Id like    '%'+@product_name_id+'%';

Where is my mistake?

Nagaraj S · Accepted Answer · 2013-12-27 06:09:39Z

2

Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,

SELECT COUNT(Product_id)+1 AS [Duplicate Id]
FROM tb_new_product_Name_id_duplicate
WHERE Product_id IN (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )

answered Dec 27, 2013 at 6:09

Nagaraj S

13.5k6 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-12-27 06:06:03Z

1

Change this part

WHERE Product_id= (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )

to using IN instead

WHERE Product_id IN (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )

Have a look at IN (Transact-SQL)

Determines whether a specified value matches any value in a subquery or a list.

answered Dec 27, 2013 at 6:06

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

1 Comment

Adriaan Stander Over a year ago

The in operator is used as explained above to determine if a value is in a given sub set or list.

Amarnath Balasubramanian · Accepted Answer · 2013-12-27 06:11:08Z

SELECT COUNT(Product_id)+1 AS [Duplicate Id]
FROM tb_new_product_Name_id_duplicate
WHERE Product_id= (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )
 ) AS B

Use Product_id IN instead of Product_id=

because if you use

Product_id = SELECT Product_id FROM tb_new_product_Name_id WHERE Product_Name=@product_name_id

it may return multiple values so use Product_id IN

so this is the correct query

SELECT COUNT(Product_id)+1 AS [Duplicate Id]
FROM tb_new_product_Name_id_duplicate
WHERE Product_id IN (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )
 ) AS B

Harshil · Accepted Answer · 2013-12-27 06:14:55Z

0

try this

WHERE Product_id in (SELECT  Product_id
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
                 )

OR

WHERE Exists (SELECT  1
                   FROM tb_new_product_Name_id
                   WHERE  Product_Name=@product_name_id 
and tb_new_product_Name_id_duplicate.Product_ID = tb_new_product_Name_id.Product_id
                 )

you can use depend on Query speed

answered Dec 27, 2013 at 6:14

Harshil

4112 silver badges7 bronze badges

Collectives™ on Stack Overflow

how to pass null value in sub query sql server

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related