Fun with Dynamic SQL - Abusing the Data Layer

I needed to do some reporting on a process from within the db, essentially output a table of values to an email for alerting purposes ... enjoy!

Period	Records	Charges
1301	55975	523110523.76
1302	58820	554884666.09
1303	0	0.00
1304	0	0.00
1305	0	0.00
1306	0	0.00
1307	0	0.00
1308	0	0.00
1309	0	0.00
1310	0	0.00
1311	0	0.00
1312	0	0.00

-------------------------------------------------------------------

ALTER PROC schema.getChargeCounts (@currentFY CHAR (2), @tOut VARCHAR (MAX) OUTPUT)
AS
BEGIN

/**************************************************************************
Joe Kelly
2012-09-17 17:43:59.300

Returns an HTML table of late charge information based up on the two digit
year entered.

DECLARE @V VARCHAR (maX) = ''
EXEC schema.getChargeCounts @currentFY = '13', @tOut = @V OUTPUT
SELECT @V

**************************************************************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

DECLARE @rowCount INT = 12
, @iter INT = 1
, @Period CHAR (4) = ''
, @tStart CHAR (256) = ''
, @tEnd CHAR (8) = ''
, @tHead CHAR (512) = 'PeriodRecordsCharges'
, @tRStart CHAR (256) = ''
, @tREnd CHAR (8) = ''
, @tDStarCC CHAR (256) = ''
, @tDStartR CHAR (256) = ''
, @tDEnd CHAR (8) = ''
, @SQL NVARCHAR (MAX) = ''

DECLARE @Report TABLE (
ident INT IDENTITY (1, 1)
, Period INT
, Records BIGINT
, Charges MONEY
)

DECLARE @ReportOut TABLE (
ident INT IDENTITY (1, 1)
, Period INT
, Records BIGINT
, Charges MONEY
)

SELECT @SQL = 'SELECT lc.Period, Count (*) Records , Sum (Charge) Charges
FROM dbo.Charge_FY' + CAST (@currentFY AS CHAR (2)) + ' LC
WHERE ' + CAST (@currentFY AS CHAR (2)) + ' = LEFT (lc.Period, 2)
GROUP BY lc.Period '

INSERT @Report (
Period
, Records
, Charges
)
EXEC sp_EXECUTESQL @SQL

WHILE (@iter <= 12)
BEGIN
SELECT @Period = @currentFY + RIGHT (CAST (REPLICATE ('0', 2) AS VARCHAR (2)) + CAST (@iter AS VARCHAR (3)), 2)

INSERT @ReportOut (
Period
, Records
, Charges
)
SELECT @Period
, 0
, 0

SELECT @iter += 1
END

UPDATE RO
SET ro.Records = r.Records
, ro.Charges = r.Charges
FROM @ReportOut RO
JOIN @Report r
ON ro.period = r.period

SELECT @tOut +=
@tStart
+ @tHead

SELECT @iter = 1

WHILE (@iter <= 12)
BEGIN
SELECT @tOut +=
@tRStart
+ @tDStarCC
+ CAST (Period AS VARCHAR (64))
+ @tDEnd
+ @tDStartR
+ CAST (Records AS VARCHAR (64))
+ @tDEnd
+ @tDStartR
+ CAST (CONVERT (MONEY, Charges, 2) AS VARCHAR (64))
+ @tDEnd
+ @tREnd
FROM @ReportOut
WHERE ident = @iter

SET @iter += 1

END

SELECT @tOut += @tEnd

SELECT @tOut = REPLACE (REPLACE (REPLACE (@tOut, ' ', ''), ' ', ''), ' ', '')

END