Take the 2-minute tour ×

I am running the following query and checking whether it returns any results and if not I need implement some logic in my sp. I have given couple of code blocks below from my sp.

select * from temp
where (select * table1 where Id='4728')=0

If the table1 doesnt have any records it shouldn't return any results and I need to know whether I am checking it correctly.

INSERT INTO #companies
    SELECT DISTINCT c.Company_Id
        INNER JOIN Request_Summary rs ON c.Company_ID=rs.Company_ID
            rs.IsMember_Ind <> 0            
            AND (SELECT COUNT(*) FROM Monitor_Request mr WHERE mr.Company_ID=c.Company_ID)< 5
                       -- I am checking it here
            AND EXISTS(SELECT COUNT(*) FROM Monitor_Request mr2 WHERE mr2.Company_ID=c.Company_ID)
            AND NOT EXISTS(
                SELECT * FROM TaskAction ta WHERE 
                ta.FirstActionTypeId = @firstActionTypeId and 
                ta.TaskTypeId = @taskTypeId and 
                ta.NextActionTypeId is not null and
                ta.EntityId = c.Company_ID and
                ta.EntityTypeId = 1)
share|improve this question
Do you have to check this explicitly? Can we see the whole SP? Depending on what you're attempting to do, you may not need to explicitly check for existence - especially if you're dealing with concurrent access. –  Clockwork-Muse Jul 3 '12 at 15:32
added the code. and commented here I need to check. –  Joshua Jul 3 '12 at 15:41
@X-Zero There you go. –  Joshua Jul 3 '12 at 15:46
add comment

4 Answers

up vote 3 down vote accepted

If the table1 doesnt have any records it shouldn't return any results

Since you're in a Stored Procedure and you may just want to do this

IF Exists(select * table1 where Id='4728')

share|improve this answer
add comment

Use WHERE EXISTS instead:

FROM temp 
    SELECT * 
    FROM table1 
    WHERE Id='4728'
share|improve this answer
+1 - pretty much where I was headed, although I tend to prefer constant values ('1', which evaluates to true in RPG...) instead of SELECT * in the EXISTS clause. –  Clockwork-Muse Jul 3 '12 at 15:30
add comment

Here's another solution:

IF (SELECT COUNT(1) FROM table1 WHERE Id='4728') = 0
    SELECT * FROM temp

and yet another solution, as pruposed by Aaron at the comments:

     SELECT * FROM temp
share|improve this answer
Great solution because it decouples the relationship between table1 and temp. –  Nick Vaccaro Jul 3 '12 at 15:35
humm why downvote? –  aF. Jul 3 '12 at 15:47
Wasn't me, but I'm not fond of this answer because getting a count is potentially much less efficient than checking for existence. While Id may be the primary key, it isn't necessarily the case, and future readers may think getting an expensive count is worthwhile even when they only care whether the count is 0 or 1. EXISTS can be no more inefficient than getting a count, so I would prefer Conrad's approach over this one, always. –  Aaron Bertrand Jul 3 '12 at 16:17
@AaronBertrand nice thought, just updated to put that solution as well :) –  aF. Jul 3 '12 at 16:21
add comment

Hmm, I think I'd restructure that query to something like this:

INSERT INTO #companies
SELECT c.company_Id  -- presumes that company_Id is DISTINCT initially
FROM Company c
              FROM Request_Summary s
              WHERE s.company_Id = c.company_Id
              AND s.isMember_Ind <> 0)
            FROM Monitor_Request m
            WHERE m.company_Id = c.company_Id
            GROUP BY m.company_Id
            HAVING COUNT(*) < 5)
                FROM TaskAction t
                WHERE ta.FirstActionTypeId = @firstActionTypeId 
                AND t.TaskTypeId = @taskTypeId 
                AND t.NextActionTypeId IS NOT NULL 
                AND t.EntityId = c.Company_ID 
                AND t.EntityTypeId = 1)

Don't ever bother with a SELECT COUNT(*) inside a WHERE EXISTS clause, as the optimizer will potentially count all rows (even if we only care about one). If a JOIN is giving you more than one row, but you're only checking for existence (ie, you don't need any data from it), use an EXISTS clause.

I was actually curious about your entire SP, not just this query - especially as you're inserting into a session/temp table. Is this something that should be turned into a CTE?

share|improve this answer
add comment

Your Answer


