Sending SQL Results in an email with tab deliminators, but varying field length is causing not to line up

declare @tableHTML nvarchar(max); 
 set @tableHTML = '<table border=1><th>Order #</th><th>Part #</th><th>Qty BO/Received</th>' 
select @tableHTML = @tableHTML + '<tr>' +
				'<td>' + Ordercolumn + '</td>' +
				'<td>' + Partcolumn + '</td>' + 
				'<td>' + cast(Qtycolumn as varchar(max)) + '</td>' + 

				'</tr>' 
from yourtable
 
set @tableHTML = @tableHTML + '</table>' 
 
select @tableHTML

Select allOpen in new window

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[ufn_ssis_import_results_email_body](@id int) RETURNS varchar(8000) AS
begin

/*
Returns a string that reports the results of a sample SQL logging table

*** WARNING:  There is a 4,000 character limitation on SSIS for passing variables into the body of an email message ***

DECLARE @id int = 211
*/	 

-- TROUBLESHOOTING ONLY, set to 0 for production, 1 for extra troubleshooting sets. 
DECLARE @debug bit = 0

Declare @msg as varchar(8000)

-- Declare @cr varchar(1) = CHAR(10), @tab varchar(1) = CHAR(9)  -- Plain Text
Declare @cr varchar(4) = '<br>', @tab varchar(5) = '<tab>'


DECLARE @rows_source bigint, @rows_inserted bigint, @rows_updated bigint, @rows_deleted bigint, @rows_no_action bigint, @rows_failed_validation bigint
DECLARE @rows_source_c varchar(20), @rows_inserted_c varchar(20), @rows_updated_c varchar(20), @rows_deleted_c varchar(20), @rows_no_action_c varchar(20), @rows_failed_validation_c varchar(20)

DECLARE @step int, @name varchar(1000), @start_dt datetime, @end_dt datetime, @success bit, @organization_name varchar(30)

-- Get the ETL dates and success flag
SELECT @start_dt = start_dt, @end_dt = end_dt, @success = success_flg FROM LOG_EXECUTIONS WHERE id = @id

/*
-- Get the client name
SELECT @organization_name = COALESCE(o.organization_name, '{Client Name Not Available}') 
FROM D_ORGANIZATION o
	JOIN LOG_EXECUTIONS ex ON o.id = ex.organization_id
WHERE ex.id = @id
*/

SELECT @organization_name = 'Add Me'

/*
IF @debug = 1
	SELECT @start_dt AS start_dt, @success AS success FROM LOG_EXECUTIONS WHERE id = @id
*/

-- Message header 
IF @success = 1
	begin
	SELECT @msg = @organization_name + ' data files were successfully loaded into the STARS Dashboard on '+ CAST(@start_dt AS varchar(20)) + '.' + @cr
	END
ELSE
	BEGIN
	SELECT @msg = @organization_name + ' data files attempted to load into the STARS Dashboard on '+ CAST(@start_dt AS varchar(20)) + ', but an error occured:' + @cr	

	SELECT @msg = @msg + 'Message: ' + CAST(error_message AS varchar(500))
	FROM dbo.LOG_ERROR_LOG
	WHERE execution_id = @id

	GOTO ex

	END

/*
IF @debug = 1
	SELECT @msg
*/
	  
-- Get the total rows inserted/updated/etc. as a bigint. 
SELECT 
	@rows_source = SUM(COALESCE(rows_source,0)), 
	@rows_inserted = SUM(COALESCE(rows_inserted, 0)),
	@rows_updated = SUM(COALESCE(rows_updated, 0)),
	@rows_deleted = SUM(COALESCE(rows_deleted, 0)),
	@rows_no_action = SUM(COALESCE(rows_no_action, 0)), 
	@rows_failed_validation = SUM(COALESCE(rows_failed_validation,0))
FROM dbo.LOG_EXECUTION_STEPS 
WHERE execution_id = @id AND step >= 100

-- Get the above as a varchar, with thousands separators. 
SELECT 
	@rows_source_c = SUM(COALESCE(rows_source, 0)),
	@rows_inserted_c = SUM(COALESCE(rows_inserted, 0)),
	@rows_updated_c = SUM(COALESCE(rows_updated, 0)),
	@rows_deleted_c = SUM(COALESCE(rows_deleted, 0)),
	@rows_no_action_c = SUM(COALESCE(rows_no_action, 0)),
	@rows_failed_validation_c = SUM(COALESCE(rows_failed_validation, 0))
FROM dbo.LOG_EXECUTION_STEPS 
WHERE execution_id = @id AND step >= 100

/*
IF @debug = 1
	SELECT 'Variables', 
		@rows_source as rows_source, @rows_inserted AS rows_inserted, @rows_updated AS rows_updated, @rows_deleted AS rows_deleted, @rows_no_action AS rows_no_action, @rows_failed_validation AS rows_failed_validation,
		@rows_source_c as rows_source_c, @rows_inserted_c AS rows_inserted_c, @rows_updated_c AS rows_updated_c, @rows_deleted_c AS rows_deleted_c, @rows_no_action_c AS rows_no_action_c, @rows_failed_validation_c AS rows_failed_validation_c
*/

/*
IF @debug = 1
	SELECT @msg
*/

-- Details
SELECT @msg = @msg + @cr + '*** Details ***' + @cr

-- Get 
Declare @row_validation_first int, @row_staging_first int, @row_insert_first int, @rows_cleanup_first int

SELECT @row_validation_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id
SELECT @row_staging_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 50
SELECT @row_insert_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 100
SELECT @rows_cleanup_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 500

-- HEADER
SELECT @msg = @msg + '<table border="2" cellspacing="0" cellpadding="1">'
SELECT @msg = @msg + '<tr><th>Task Name</th><th>Rows in Source</th><th>Inserted</th><th>Updated</th><th>Deleted</th><th>Validation Failures</th><th>No Action</th><th>Load Time</th></tr>'

-- TOTALS ROW
SELECT @msg = @msg +
	'<tr>' +
	--'<td></td>' +
	'<td>TOTAL</td>' +
	'<td>' + @rows_source_c + '</td>' +
  	'<td>' + @rows_inserted_c + '</td>' +  
	'<td>' + @rows_updated_c + '</td>' +
	'<td>' + @rows_deleted_c + '</td>' +
	'<td>' + @rows_failed_validation_c  + '</td>' +
	'<td>' + @rows_no_action_c + '</td>' +
	'<td>' + DATEDIFF(d, @start_dt, @end_dt) + '</td>' + 
	'</tr>'


-- DETAIL ROWS
DECLARE cur CURSOR FOR 
SELECT step, name, rows_source, rows_inserted, rows_updated, rows_deleted, rows_no_action, rows_failed_validation, start_dt, end_dt
FROM SAMPLE_TABLE_NAME
WHERE execution_id = @id
ORDER BY id

OPEN cur

FETCH NEXT FROM cur 
INTO @step, @name, @rows_source, @rows_inserted, @rows_updated, @rows_deleted, @rows_no_action, @rows_failed_validation, @start_dt, @end_dt

WHILE @@FETCH_STATUS = 0
BEGIN

	-- Sub-row headers 
	IF @step = @row_validation_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Validations</b></td></tr>'

	IF @step = @row_staging_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Staging</b></td></tr>'

	IF @step = @row_insert_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Insert</b></td></tr>'

	IF @step = @rows_cleanup_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Cleanup</b></td></tr>'

	SELECT @msg = @msg + '<tr>'

	SELECT @msg = @msg +
		-- '<td>' + CAST(@step AS varchar(MAX)) + '</td>' +
		'<td>' + @name + '</td>' +
		'<td>' + @rows_source + '</td>' +
  		'<td>' + @rows_inserted + '</td>' +
		'<td>' + @rows_updated + '</td>' +
		'<td>' + @rows_deleted + '</td>' +
		'<td>' + @rows_failed_validation  + '</td>' +
		'<td>' + @rows_no_action + '</td>' +
		'<td>' + DATEDIFF(d, @start_dt, @end_dt) + '</td>'

	SELECT @msg = @msg + '</tr>'

    FETCH NEXT FROM cur 
    INTO @step, @name, @rows_source, @rows_inserted, @rows_updated, @rows_deleted, @rows_no_action, @rows_failed_validation, @start_dt, @end_dt
END 
CLOSE cur;
DEALLOCATE cur;

SELECT @msg = @msg + '</table>'

-- Make sure the return value is less than 4,000 characters
IF LEN(@msg) > 3990  
	SET @msg = LEFT(@msg, 3900) + '{error: The length of this message is greater than 4,000 characters, please contact support to fix.}'

ex:
	RETURN @msg
	end

--SELECT @msg
--GO

Select allOpen in new window