CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ces1919
Ces1919

Reputation: 21

IF statement with Transact SQL

When I execute my transact SQL code 

IF (SELECT model_id FROM request_unit where request_id = '4357') IS NULL
    SELECT part_id FROM request_unit WHERE request_id = '4357'
ELSE
     SELECT model_id FROM request_unit where request_id = '4357'

I receive the following error:

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

Upvotes: 1

Views: 139

Answers (5)

neer
neer

Reputation: 4082

You can do it like;

COALESCE

SELECT COALESCE(model_id, part_id) FROM request_unit WHERE request_id = '4357'

ISNULL

SELECT ISNULL(model_id, part_id) FROM request_unit WHERE request_id = '4357'

CASE WHEN

SELECT CASE WHEN model_id IS NULL THEN part_id ELSE model_id END FROM request_unit WHERE request_id = '4357'

IIF Starting with 2012

SELECT IIF(model_id IS NULL, part_id, model_id) THEN part_id ELSE model_id END FROM request_unit WHERE request_id = '4357'

Upvotes: 0

chettiarsantosh
chettiarsantosh

Reputation: 1

Along with @GordonLinoff's answer you can write the following in order to get the column name:

SELECT COALESCE(model_id, part_id) as 'YOURCOLUMNNAME'
FROM request_unit 
WHERE request_id = '4357';

Upvotes: 0

Arun Prasad E S
Arun Prasad E S

Reputation: 10125

  IF (SELECT top 1 model_id FROM request_unit where request_id = '4357') IS NULL
    SELECT part_id FROM request_unit WHERE request_id = '4357'
ELSE
     SELECT model_id FROM request_unit where request_id = '4357'

This too can be used, but the @Gordon Lioff's answer is preferred

Upvotes: 0

Sankar
Sankar

Reputation: 7107

Try this...

It means there are several model_id for the request_id = '4357'

IF Not Exists (SELECT model_id FROM request_unit where request_id = '4357')
    SELECT part_id FROM request_unit WHERE request_id = '4357'
ELSE
     SELECT model_id FROM request_unit where request_id = '4357'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

How about just using COALESCE()?

SELECT COALESCE(model_id, part_id)
FROM request_unit 
WHERE request_id = '4357';

The IF is not needed.

The error itself seems pretty self-explanatory. There is more than one row in request_unit where the request_id is 4357.

Upvotes: 5

Related Questions