IF ...Else

Can you use IF Stmt to check for a particular value?

For instance:

IF (select lm.BCID = 2)
From lm INNER JOIN lc
ON lm.lid = lc.lid
WHERE lc.ecid = 66
BEGIN
SELECT stmts.....
END

ELSE

IF (select lm.BCID = 7)
FROM lm INNER JOIN lc
ON lm.lid = lc.lid
WHERE lc.ecid = 67
BEGIN
SELECT stmts.....
END

I'm receiving error messages in my syntax but I'm not why. I thought maybe because I am not passing a boolean expression.

I've modified the code to this:

IF (select lm.BCID From lm INNER JOIN lc
ON lm.lid = lc.lid
WHERE lc.ecid = 66) = 2
BEGIN
SELECT stmts.....
END

ELSE

IF (select lm.BCID
FROM lm INNER JOIN lc
ON lm.lid = lc.lid
WHERE lc.ecid = 67) = 7
BEGIN
SELECT stmts.....
END

And my syntax is ok but now the errors I get are the following:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Line 76
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I kinda why but I'm not sure of how to fix it. Any ideas?

In your IF statements you are nesting a query. You can only use this type of statement if it returns only one result. I assume that the nested query has many rows that meet that criteria.

You could do several things. You can use SELECT...INTO for your nested query, selecting the results into a separate table. This table would contain only the matching results. If you rebuild the table each time your query runs, you can then use the second table to perform whatever action you wish.

try your query :

select lm.BCID From lm INNER JOIN lc
ON lm.lid = lc.lid
WHERE lc.ecid = 66

i guess that query return more than 1 row..
if u use any expression, it only work if there is only 1 row for comparisson.



Regards,

Tonny Soeroso