Checking that data has been created in multiple tables

I have 4 tables that I want to check for data. When there are Rows in all the tables, I want to print a message for success. When there aren't any rows in any table I print an error (when 1 or more table is missing data), I want to warn (unless they are all missing data).

The following script does exactly what I want but I think it could be done in a clearer, more succinct manner.

```
USE STG_RM_Connector
GO

DECLARE @tblAttCode sysname, @tblAttSess sysname, @tblStaffServ sysname, @tblStuSer sysname
DECLARE @rowCntAttCode int, @rowCntAttSess int, @rowCntStaffServ int, @rowCntStuSer int

SET @tblAttSess = 'Attendance Session Pupil Data'
SET @tblAttCode = 'Attendace Code Service Attendace Codes'
SET @tblStaffServ = 'Staff Service Staff Members'
SET @tblStuSer = 'Student Service Pupil Data'

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntAttSess = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttSess
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntStaffServ = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblStaffServ
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntStuSer = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblStuSer
AND IDX.index_id < 2
GROUP BY TBL.objec

As I mention in this other SQL answer:


Any SQL query has up to eight primary clauses. WITH, SELECT, INTO, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Everything else is a part of one of these clauses. These 8 clauses should have zero indentation relative to the query as a whole, and everything else should be indented by at least one level.

A little indentation can go a very long way to making your query far more readable.

So, just using your first SELECT as an example, proper indentation would look something like this:

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

If you insist on turning any of these single lines into multiple lines to reduce the width, then the extra lines should get an extra level of indentation. For example

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
        AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode 
    AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

And some people like breaking the ON part of a join onto its own line, which should look like this:

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART
        ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX 
        ON PART.object_id = IDX.object_id
            AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode 
    AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

Any of these three indention styles are fine, but be consistent, no matter what you pick. Hopefully, you can see how this sort of indention style helps guide the reader's eye to the important parts of the query.

Now, with the indentation out of the way... I'm a little concerned about how exactly your query is even working.

Normally, when we're doing a variable assignment in a select, we want to use TOP 1 just to be certain we're only getting one row back. Given that you're using an aggregation query, you don't necessarily need to do this... but why are you using a GROUP BY? What sort of results do you expect? The TBL.name is redundant in the GROUP BY since you already have a WHERE TBL.name = @tblAttCode, so there can only be one value in that column anyway. What are you hoping to accomplish with the GROUP BY TBL.object_id?

StackExchange Code Review Q#87532, answer score: 3