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