Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: T-SQL to check for empty recordset

    Is there a way to use T-SQL to check to see if a table or recordset is empty using T-SQL. Prefer not to use Cursors if possible.

  2. #2
    Join Date
    Apr 2009
    Posts
    6
    Why dont you use count on the recordset. It will give you 0 if the recordset is empty.

    select count(*)
    from tableName

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I sense that you aren't explaining what you want/need well enough for us to figure out what you really want.

    Count(*) will do exactly what you've described.

    The rs.count attribute will give you what you've described without the need for any additional code, if you have a recordset object in your code.

    I still have a nagging feeling that you want something other than what you've asked for, and if that is the case please try to ask again with a few more details.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2011
    Posts
    1

    Better way...

    while count(*) will work, there are some things to consider about this. First of all, if you arent really interested in the number of records, just that some exist, you really dont want to make your database do all that extra work. Trust me, shavings make a pile when it comes to effieciency.

    If you are looking to see if there are records present, then I think its safe to say that you want to do something if there are or are not records there. To do this, use the
    if exists(select <key or index field> from table 1 (NOLOCK) where .......)
    begin
    do stuff
    end

    Ok, so if there are records, it will go and execute the do stuff area. If you want no records use if NOT exists (
    you can also use an else after the begin..end if you want to do soemthing for both conditions.

    A few other notes
    - Do not use * unless you have absolutely no other choice. It forces the system to go into the catalogue and look up all the field names so that it can return them all to you. Also, why return 10 fields when you only need 1?

    -Always fully declare fields, it makes the SQL more efficient table1.field1

    - Unless you need the table locked, use the NOLOCK clause. One table exclusively locked can cause a whole system to fail due to the log jam behind that one table access

    - Only look for the 1 field, and make that one field part of the key or index

    good luck!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ApplicationDBA View Post
    - Do not use * unless you have absolutely no other choice. It forces the system to go into the catalogue and look up all the field names so that it can return them all to you.
    i've heard this before and i don't think it's an issue

    suppose instead of this --

    SELECT * FROM daTable

    you did this instead --

    SELECT foo,bar,fap FROM daTable

    wouldn't the system be "forced" to go into the catalogue and look up the field names in this query as well, so that it can detect that "fap" is actually not a valid column in the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •