How to check if a table has at least one record in it ?

I have a table, and I want to know if it has at least one record in it. If it the table does not have a record, then I want sql code execution to exit the stored procedure.
I came up with the following code, however I don't know if it is correct or not? I need help from an expert.

-- Code below is from stored procedure.
if not exists (select top 1 uid
               from user_detail
               where uid IS NOT NULL)
  retur
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
bbaoIT ConsultantCommented:
it looks fine for me.

have you tried it using MS SQL?
0
 
Mark BullockQA Engineer IIICommented:
EXISTS test for existence of rows.
The "top 1 uid" is not necessary because the sub-query can return any number of rows and result in a true value. You could use "uid" or "1" instead.
0
 
brgdotnetcontractorAuthor Commented:
When I run the query in Query Analyzer, it just comes back with an empty column. Anyway, I don't think my solution is the best approach. Perhaps someone can offer another solution. Plus how do I exit the stored procedure if the row does not exists? I put a return statement below, but I don't know if that is the correct syntax?

if not exists (select top 1 EmptyColumn
                from Table_3)
   return
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Mark BullockQA Engineer IIICommented:
Run the subquery, select top 1 EmptyColumn from Table_3. If you get records, the not exists statement will evaluate to false.

The return statement should work.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your code it's OK. There is no better way to do it.
if not exists (select top 1 EmptyColumn 
                 from Table_3) 
    return 

Open in new window

0
 
Scott PletcherSenior DBACommented:
I use a static 1 as the column, to avoid errors just in case the table definition changes, and the column you checked went away, although you directly reference a column anyway, so you could use "uid" instead of 1:

if not exists (select top (1) 1
        from dbo.user_detail
        where uid IS NOT NULL)
   return

Btw, you should add the parentheses around the "top" row count value, as that will be required in a future version of SQL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.