troubleshooting Question

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

Avatar of Jeremy Poisson
Jeremy PoissonFlag for United States of America asked on
Microsoft SQL ServerSQL* Stored Proc
7 Comments1 Solution347 ViewsLast Modified:
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'
SQL_Select_Statement_Fine.png
ASKER CERTIFIED SOLUTION
Snarf0001

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros