Link to home
Start Free TrialLog in
Avatar of Jeremy Poisson
Jeremy PoissonFlag for United States of America

asked on

SQL Stored Procedure - Error converting data type varchar to numeric.

User generated imageGood afternoon Experts,

  I'm attempting to finalize a stored procedure that will allow users to current year to previous year (exact period over period; e.g. current year to date - 2/22/2019 versus previous year to date as of 2/22/2018) however, I'm running into an error I can't seem to get past. For whatever reason, the SP is throwing an error converting data type varchar to numeric. I cannot figure out where I've gone astray.

When I run basic select statements I have no issue but, when I execute the SP, the error appears. And, the line that the error references (81) is a blank area of the stored procedure, so not really helpful.

I've included the entire code for the SP below and a couple of screenshots. Please let me know if you need any additional details.

Thank you!



(2 row(s) affected)

(1 row(s) affected)
Msg 8114, Level 16, State 5, Procedure Herman_SSRS_Direct_VS_Regular_Shipments, Line 101
Error converting data type varchar to numeric.



USE [EdiTranslator]
GO
/****** Object:  StoredProcedure [dbo].[Herman_SSRS_Direct_VS_Regular_Shipments]    Script Date: 02/22/2019 09:31:19 ******/

SET ANSI_WARNINGS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Herman_SSRS_Direct_VS_Regular_Shipments]
@xVendorID varchar(max),
@xEndDate Datetime
AS
BEGIN


Create table #S


(SupplierID varchar(max),
 Supplier_Name varchar(255),
 PreYr Decimal(18,6),
 CurYr Decimal(18,6))



Select x.* into #T from --- CURRENT YEAR
(Select invline.supplier_id AS SupplierID, S.supplier_name, YEAR(hdr.ship_date) as Yr, 
COUNT(CASE WHEN (isnull(direct_shipment,0)) = 'N' AND direct_shipment IS NOT NULL then direct_shipment END) AS Count_of_Regular_Lines,
---COUNT(CASE WHEN direct_shipment = 'Y' then extended_price END) AS Count_of_Direct_Lines,

--SUM(CASE WHEN direct_shipment = 'N' then extended_price END) AS Value_of_Regular_Lines,
--SUM(CASE WHEN direct_shipment = 'Y' then extended_price END) AS Value_of_Direct_Lines,

365 - DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @xEndDate), '19000101'),DATEADD(d, -0, DATEDIFF(d, 0, @xEndDate))) as Days_Remaining

		from 
CommerceCenter.dbo.p21_view_oe_pick_ticket as hdr
inner join CommerceCenter.dbo.p21_view_oe_pick_ticket_detail as line
on hdr.pick_ticket_no=line.pick_ticket_no
inner join CommerceCenter.dbo.p21_view_invoice_line as invline
on invline.invoice_line_uid=line.invoice_line_uid inner join
		CommerceCenter.dbo.supplier S on invline.supplier_id = S.supplier_id AND S.delete_flag = 'N' AND S.buyer_id is not null and ISNUMERIC(S.supplier_name) = 0
where 
	Year(hdr.ship_date)= Year(@xEndDate) and 
	DATEADD(dd, 0, DATEDIFF(dd, 0, hdr.ship_date)) <= @xEndDate 
	and S.supplier_name = @xVendorID 
group by   invline.supplier_id, S.supplier_name, YEAR(hdr.ship_date)
having sum(invline.extended_price)<>0







union all --- PREVIOUS YEAR






Select invline.supplier_id AS SupplierID, S.supplier_name, YEAR(hdr.ship_date) as Yr, 
COUNT(CASE WHEN (isnull(direct_shipment,0)) = 'N' AND direct_shipment IS NOT NULL then direct_shipment END) AS Count_of_Regular_Lines,
---COUNT(CASE WHEN direct_shipment = 'Y' then extended_price END) AS Count_of_Direct_Lines,

--SUM(CASE WHEN direct_shipment = 'N' then extended_price END) AS Value_of_Regular_Lines,
--SUM(CASE WHEN direct_shipment = 'Y' then extended_price END) AS Value_of_Direct_Lines,
365 - DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @xEndDate), '19000101'),DATEADD(d, -0, DATEDIFF(d, 0, @xEndDate))) as Days_Remaining
from
		CommerceCenter.dbo.p21_view_oe_pick_ticket as hdr
inner join CommerceCenter.dbo.p21_view_oe_pick_ticket_detail as line
on hdr.pick_ticket_no=line.pick_ticket_no
inner join CommerceCenter.dbo.p21_view_invoice_line as invline
on invline.invoice_line_uid=line.invoice_line_uid inner join
		CommerceCenter.dbo.supplier S on invline.supplier_id = S.supplier_id AND S.delete_flag = 'N' AND S.buyer_id is not null and ISNUMERIC(S.supplier_name) = 0
where 
	Year(hdr.ship_date)= Year(@xEndDate)-1 and 
	DATEADD(dd, 0, DATEDIFF(dd, 0, hdr.ship_date)) <= (DATEADD(year, -1, @xEndDate)) 
	and S.supplier_name = @xVendorID 
group by  invline.supplier_id, S.supplier_name, YEAR(hdr.ship_date)
having sum(invline.extended_price) <>0) X


Insert into #S 
Select Distinct  SupplierID, supplier_name, 0,0 from #T

update #S set PreYr=X.Total_Regular_Line from
(Select count(isnull(Count_of_Regular_Lines,0)) as Total_Regular_Line, SupplierID from #T where Yr=Year(@xEndDate)-1
group by SupplierID) X where X.SupplierID=#S.supplier_name

update #S set CurYr=X.Total_Regular_Line from
(Select count(isnull(Count_of_Regular_Lines,0)) as Total_Regular_Line, SupplierID from #T where Yr=Year(@xEndDate)
group by SupplierID) X where X.SupplierID=#S.SupplierID






Select *, (CurYr-PreYr) as Diff, case when PreYr<>0 then cast((CurYr-PreYr)/PreYr AS Decimal(18,6)) else 1 END AS YOY from #S





drop table #T
drop table #S
END


---exec Herman_SSRS_DIRECT_VS_REGULAR_SHIPMENTS 'Amphenol RF', '02-22-2019'

Open in new window

SQL_Select_Statement_Fine.png
Avatar of Aneesh
Aneesh
Flag of Canada image

When you call the sp use the format YYYYMMDD

exec Herman_SSRS_DIRECT_VS_REGULAR_SHIPMENTS 'Amphenol RF', '20190222'

if you still have this error can you confirm which statement is causing the error , like#101 seems like
Select *, (CurYr-PreYr) as Diff, case when PreYr<>0 then cast((CurYr-PreYr)/PreYr AS Decimal(18,6)) else 1 END AS YOY from #S

you can comment that line and see whether it runs or not. once you have the staetment we can fix it.
Avatar of Jeremy Poisson

ASKER

Hi Aneesh,

  Thank you for your quick follow-up and offer to help with this!

I changed the date format for the parameter and still the exact same error (screenshot 1). Additionally, I commented out the line your suggested, and the issue persists (screenshot 2)
SQL_SP_Error_Converting_Data_Type_Va.png
SQL_SP_Error_Converting_Data_Type_Va.png
Sorry I misread the error. Can you comment the statements one by one from the bottom , then run the sp and see which one runs. (if the sp runs uncomment that line and comment the line above) that way its easy to determine the error location.
ASKER CERTIFIED SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good god....how embarrassing, but glad I reached out. I would have probably overlooked that another 40 times before I caught it - time to take a break :)

Snarf, thank you...We're looking good now.
SQL_SP_Error_Converting_Data_Type_Va.png
Great, and understandable, those are SUCH a pain!
Thank you, Experts! You've come through again.


Jeremy