CODEWITHSUNDEEP
sqlsql-server
mircea .
mircea .

Reputation: 241

SQL Select statement check condition

How should i go about doing something like:

IF the following select has any rows

SELECT ID, NAME FROM TABLE WHERE ID=@ID

then return that actual select, otherwise use another select statement.

I use somwthing like

IF EXISTS(SELECT ID, NAME FROM TABLE WHERE ID=@ID)
    SELECT ID, NAME FROM TABLE WHERE ID=@ID
ELSE
    SELECT QTY FROM TABLE WHERE ID=@ID

but this way i use the first select twice(once in the check and once the actual return record set.

Upvotes: 2

Views: 6473

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65304

DECLARE @ID INT, 
        @NAME VARCHAR(50);

SELECT @ID = ID, 
       @NAME = NAME 
FROM TABLE WHERE ID=@ID;

IF(@@ROWCOUNT = 1) 
  SELECT @ID AS ID, @NAME AS NAME
ELSE   
  SELECT QTY FROM TABLE WHERE ID=@ID

Upvotes: 2

MatBailie
MatBailie

Reputation: 86765

It's not as inefficient as you think. The EXISTS keyword means that the optimiser stops after 1 record is found.

But, if you really want to avoid repetition, you can do something like...

SELECT ID, NAME FROM TABLE WHERE ID=@ID

IF (@@rowcount = 0)
    SELECT QTY FROM TABLE WHERE ID=@ID

The problem here is that you now return up to 2 record sets to the client. The first of which my be empty (but field headers, etc, are still returned - just with 0 records).

You could avoid that by inserting results into a table variable, and only returning them to the client if there are any records. But I think it would be noticeably slower than just using EXISTS() like you already do.

Upvotes: 2

Related Questions