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

Jeremy Poisson
Jeremy Poisson used Ask the Experts™
on
SQL SP Error converting data type varchar to numericGood 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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
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.
Jeremy PoissonPresident

Author

Commented:
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
AneeshDatabase Consultant
Top Expert 2009

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Your second last update statement has the following:

group by SupplierID) X where X.SupplierID=#S.supplier_name

comparing supplierID with supplier_name - based on the data in your screenshot, I'd guess that's the issue
Jeremy PoissonPresident

Author

Commented:
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!
Jeremy PoissonPresident

Author

Commented:
Thank you, Experts! You've come through again.


Jeremy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial