I've spent an inordinate amount of time on getting this one stored proc to work, it's basically putting results from the query into an html format that is getting emailed. The query that the proc collects data from works fine and returns results as expected however when the output is attempted to be put into the table for the email it fails with the arithmetic overflow error.
DECLARE @sCompanyName varchar(50) = 'ITT Discrepancy'
DECLARE @sEmailTo varchar(128) = ''
DECLARE @sMailProfile varchar(128) = 'Database Notification Profile'
-- Other declarations
DECLARE @sEMailSubject varchar(100) = @sCompanyName + convert(varchar(10),Convert(date,getdate()))
DECLARE @sMessageBody varchar(max) = '', @sITT varchar(max) = '', @iMailItemID int = 0
-- Exit if there are no records to process
IF NOT EXISTS(select ORDDOCID, ITEMNMBR, TRNSFQTY, case when ct is null then 0 else ct end as 'SerialsScanned', TRNSFQTY-ct as 'Diff' from
(select ORDDOCID, ITEMNMBR,TRNSFQTY from ZTEST.dbo.SVC00701 where TRNSFLOC = 'TS-PF' and STATUS in (3,4)) as gp
left join
(select OrderNumber, ItemNumber, COUNT(serial) as 'ct' from ManualScan.dbo.SoldItems where TranType = 'ITT' group by OrderNumber, ItemNumber) as srl on gp.ORDDOCID = srl.OrderNumber and gp.ITEMNMBR = srl.ItemNumber
where TRNSFQTY-(case when ct is null then 0 else ct end)>0)
RETURN
-- Set the header
SET @sMessageBody = '<html><head><style type="text/css">
.style1
{
width: 100%;
border-style: solid;
border-width: 1px;
}
</style>
</head>
<p style="font-size: large; color: #CC3300;">
' + @sCompanyName + '
</p>'
-- Set the Items Received
DECLARE @sITTnum varchar(31), @sItemNum varchar(31), @sITTQty numeric(19,5), @sSrlQty numeric(18,0), @sDiff numeric(18,0)
DECLARE cITT CURSOR FOR
select ORDDOCID, ITEMNMBR, cast(TRNSFQTY as numeric(18,0)), cast(case when ct is null then 0 else ct end as numeric(18,0)) as 'SerialsScanned', cast(TRNSFQTY as numeric(18,0))-cast(case when ct is null then 0 else ct end as numeric(18,0)) as 'Diff'
from
(select ORDDOCID, ITEMNMBR,cast(TRNSFQTY as numeric(18,0)) as 'TRNSFQTY' from ZTEST.dbo.SVC00701 where TRNSFLOC = 'TS-PF' and STATUS in (3,4)) as gp
left join
(select OrderNumber, ItemNumber, cast(case when COUNT(serial) is null then 0 else COUNT(serial) end as numeric(18,0)) as 'ct' from ManualScan.dbo.SoldItems where TranType = 'ITT' group by OrderNumber, ItemNumber) as srl on gp.ORDDOCID = srl.OrderNumber and gp.ITEMNMBR = srl.ItemNumber
where TRNSFQTY-(case when ct is null then 0 else ct end)>0
OPEN cITT
FETCH NEXT FROM cITT INTO @sITTnum, @sItemNum, @sITTQty, @sSrlQty, @sDiff
WHILE @@FETCH_STATUS = 0
BEGIN
`it says error occurs at this line...`
SELECT @sITT = @sITT + '<tr><td style="border-style: solid; border-width: thin">' + @sITTnum +
'</td><td style="border-style: solid; border-width: thin">' + @sItemNum +
`but if I comment out from these lines it works fine so something in these values/variables`
'</td><td style="border-style: solid; border-width: thin">' + @sITTQty +
'</td><td style="border-style: solid; border-width: thin">' + @sSrlQty +
'</td><td style="border-style: solid; border-width: thin">' + @sDiff +
'</td></tr>'
FETCH NEXT FROM cITT INTO @sITTnum, @sItemNum, @sITTQty, @sSrlQty, @sDiff
END
CLOSE cITT
DEALLOCATE cITT
IF @sITT <> ''
SET @sITT = '<table class="style2"
style="font-family: "Times New Roman", Times, serif; border-style: solid; border-width:thin">
<tr>
<td style="border-style: solid; border-width: thin" >
<b>ITT Num</b></td>
<td style="border-style: solid; border-width: thin" >
<b>Item Number</b></td>
<td style="border-style: solid; border-width: thin">
<b>ITT Quantity</b></td>
<td style="border-style: solid; border-width: thin">
<b>Scanned Quantity</b></td>
<td style="border-style: solid; border-width: thin">
<b>Difference</b></td>
</tr>' + @sITT + '</table></body></html>'
SET @sMessageBody = @sMessageBody + @sITT
EXEC msdb..sp_send_dbmail
@profile_name = @sMailProfile,
@body_format = 'HTML',
@recipients = @sEmailTo,
@subject = @sEMailSubject,
@body = @sMessageBody,
@mailitem_id = @iMailItemID OUTPUT
These are the results of the query:
ORDDOCID ITEMNMBR (No column name) SerialsScanned Diff
T100742 APP-MU8X2LL/A 100 0 100
Any guidance would be extremely appreciated.
In the failing line you essentially have:
So you are trying to add a numeric to a varchar. This attempts to promote the varchar data to numeric, and that is why you are getting the error.
Add an explicit cast to
@sITTQtyEdit: You will have to do the same with
@sDiffand@sSrlQty