Avatar of Jeremy Poisson
Jeremy Poisson
Flag for United States of America asked on

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
Microsoft SQL ServerSQL* Stored Proc

Avatar of undefined
Last Comment
Jeremy Poisson

8/22/2022 - Mon
Aneesh

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 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
Aneesh

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Snarf0001

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeremy Poisson

ASKER
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
Snarf0001

Great, and understandable, those are SUCH a pain!
Jeremy Poisson

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


Jeremy
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.