Number format troubles (*Resolved*)

[Granty]

Hey folks,

Below is part of a stored proc I have in SQL Server

CREATE PROCEDURE dbo.sp_Alerts_Billing_SendMail AS

DECLARE @SendTo VARCHAR(100)
DECLARE @MessageSubject VARCHAR(500)
DECLARE @MessageContent VARCHAR(4000)

DECLARE @TotalErrors VARCHAR(12)
DECLARE @TotalValue VARCHAR(12)

DECLARE @Date VARCHAR(12)
DECLARE @Table VARCHAR(10)
DECLARE @Code VARCHAR(12)
DECLARE @CDRToday VARCHAR(12)
DECLARE @CDRYesterday VARCHAR(12)
DECLARE @CDRChange VARCHAR(12)
DECLARE @ValToday VARCHAR(12)
DECLARE @ValYesterday VARCHAR(12)
DECLARE @ValChange VARCHAR(12)

SELECT @TotalErrors = SUM(intTotalToday), @TotalValue = ROUND(sum(fltChargeToday),2)
FROM tblBillingAlerts

SELECT @Date = dtmDateTime
FROM tblBillingAlerts

SET @MessageContent = dbo.PadString('Total Errors', 18) + dbo.PadString('Total Value', 18) + '\r\n' 
SET @MessageContent = @MessageContent + dbo.PadString(@TotalErrors, 18) + dbo.PadString(@TotalValue, 18) + '\r\n\r\n\r\n'
SET @MessageContent = @MessageContent + dbo.PadString('Table', 10) + dbo.PadString('Code', 10) + dbo.PadString('CDR Today', 16)
					       + dbo.PadString('CDR Yesterday', 16) + dbo.PadString('CDR Change(%)', 16) + dbo.PadString('Val Today(£)', 16) + dbo.PadString('Val Yesterday(£)', 18)
					       + dbo.PadString('Val Change(%)', 16) + '\r\n'


DECLARE AlertCursor CURSOR	LOCAL FAST_FORWARD 
FOR
SELECT nvchrSource, vchrErrorCode, intTotalToday, intTotalYesterday, fltTotalChange, fltChargeToday, fltChargeYesterday, fltChargeChange
FROM tblBillingAlerts
WHERE fltTotalChange > fltNumberThreshold 
OR fltChargeChange > fltValueThreshold
ORDER BY nvchrSource, vchrErrorCode

OPEN AlertCursor

FETCH NEXT FROM AlertCursor
INTO @Table, @Code, @CDRToday, @CDRYesterday, @CDRChange, @ValToday, @ValYesterday, @ValChange


WHILE @@FETCH_STATUS = 0
BEGIN

	SET @MessageContent = @MessageContent + dbo.PadString(@Table, 10) + dbo.PadString(@Code, 10) + dbo.PadString(@CDRToday, 16) 
						       + dbo.PadString(@CDRYesterday, 16) + dbo.PadString(@CDRChange, 16) + dbo.PadString(@ValToday, 16) 
						       + dbo.PadString(@ValYesterday, 18) + dbo.PadString(@ValChange, 16) + '\r\n'
	
	FETCH NEXT FROM AlertCursor
	INTO @Table, @Code, @CDRToday, @CDRYesterday, @CDRChange, @ValToday, @ValYesterday, @ValChange
	
END

The problem is not a big one, just an irritating one :P

When the Value of @TotalErrors is over 1 million, it outputs to my mail as 1.00506e+006. I would just like it to be a normal number, not some hideous scientific thing.

Anyone have any ideas how I can solve this?

Cheers
J.

[PilgrimPete]

Where is it being converted - in the sp or in the calling process?

Have you tried declaring @TotalErrors as an int and explicitly casting/converting it to a varchar?

[Granty]

The boy done good

Cheers PilgrimPete

[PilgrimPete]

Glad to hear it.
Weird behaviour though...