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

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
Jeremy PoissonPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase ConsultantCommented:
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 PoissonPresidentAuthor 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
Aneesh RetnakaranDatabase ConsultantCommented:
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.
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Snarf0001Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeremy PoissonPresidentAuthor 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
Snarf0001Commented:
Great, and understandable, those are SUCH a pain!
Jeremy PoissonPresidentAuthor Commented:
Thank you, Experts! You've come through again.


Jeremy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.