Checking that data has been created in multiple tables

Question

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.object_id, TBL.name;
IF @rowCntAttCode = 0 AND @rowCntAttSess = 0 AND @rowCntStaffServ = 0 AND @rowCntStuSer = 0
BEGIN
 RAISERROR ('Failed to pull data for all tables', 16, 1)
END
ELSE IF @rowCntAttCode = 0 OR @rowCntAttSess = 0 OR @rowCntStaffServ = 0 OR @rowCntStuSer = 0
 BEGIN
 RAISERROR ('Failed to pull data for one of the RM staging tables', 10, 1)
 END 
ELSE 
BEGIN
 PRINT 'Successfully pulled data for all RM staging tables'
END
GO

Answer 1

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?

• aydjay: To be honest I don't know why i'm doing these aggregations. I have pinched that from a stackoverflow post which I can't find now but I think: blogs.msdn.com/b/martijnh/archive/2010/07/15/… will give an idea of the rationale. I think i should probably use method 4 in that blog post instead.
• nhgrif: None of the four methods there have a GROUP BY.