4
\$\begingroup\$

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
\$\endgroup\$
0

1 Answer 1

Reset to default
3
\$\begingroup\$

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?

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$ Commented Apr 22, 2015 at 8:12
  • \$\begingroup\$ None of the four methods there have a GROUP BY. \$\endgroup\$ Commented Apr 22, 2015 at 10:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.