• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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
0
brgdotnet
Asked:
brgdotnet
5 Solutions
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now