I typically put an existence guard when I do a DROP TABLE: IF OBJECT_ID('tempdb..#tblHOLD') IS NOT NULL DROP TABLE #tblHOLD
Even for temp tables, I would use meaningful names, especially with 0 comments.
CAST(CAST(CAST(NullRows AS VARCHAR(10))+'.00' AS DEC(15,2))/CAST(CAST(TotalRows AS VARCHAR(10))+'.00' AS DEC(15,2))*100 AS DEC(5,2)) AS PercentageNull
I'm not sure of the purpose of the above statement. If it because dividing INTS gives an INT, CAST(NullRows as REAL) / CAST(TotalRows AS REAL). Even if there is a reason for CAST as VARCHAR, why append +'.00' when you are putting it in a DEC(15,2)?
The above statement will fail when TotalRows or NullRows > 9999999999 when you try to cast as VARCHAR(10). BIGINT can hold 9,223,372,036,854,775,807. If there is some reason for CASTing as VARCHAR and appending '.00' of which I am unaware, they should be VARCHAR(20). Similarly, DEC(15,2) is too small. A table could theoretically have as many rows as can fit in 524+ Terrabytes, so certainly > 9999999999.
Finally, it fails if there are any empty tables, I'd replace with
CASE WHEN TotalRows != 0
THEN CAST(CAST(NullRows AS REAL)/CAST(TotalRows AS REAL)*100 AS DEC(5,2)) -- DEC(15,2) is purely formatting
END AS PercentageNull