Number format troubles (*Resolved*)
Hey folks,
Below is part of a stored proc I have in SQL Server
Code:
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.
