All geek questions in one place

The difference between executing a stored procedure and a query (arithmetic overflow due to types)

Question: why there is a difference in how data types are converted / interpreted during query execution and stored procedure?

I have a stored procedure that gives me:

Msg 8115, Level 16, State 2, Procedure sp****ChargePackagingTest, Line 9 Arithmetic overflow error converting expression to data type nvarchar.

The problem is that it only executes when the stored procedure is executed:

 exec [sp****ChargePackagingTest] '2016-10-14', '2016-10-25'

However, if I execute an identical query (copied outside the stored procedure and declared with the same parameters and using SQL management studio), I do not experience a problem.

In addition, I know the exact location of the problem: CONVERT (nvarchar (2), JobPDF.Pages / 2)

The stored procedure says that the problem is on line 9, but in fact it is not. If I change it to nvarchar (3), it works even then, acting as a stored procedure.

However, JobPdf.Pages is a null value: int is a value: only 24, 48, 72 (I checked). In addition, it fails only when there is enough data, i.e. Not the date range, but the length of the date range.

Stored Procedure Code:

 ALTER PROCEDURE [dbo].[sp****ChargePackagingTest] @dateFrom DateTime, @dateTo DateTime AS BEGIN SELECT costPerOrder****.[Product Name], costPerOrder****.[Pack Price], SUM([Pack Count]) as [Pack Count], SUM(costPerOrder****.[Packaging Cost]) as [Packaging Cost] FROM ( -- Raw data aggregated by order SELECT [Product Name], [Pack Price] , CEILING(SUM(Quantity) * 1.0 / [Pack Items]) as [Pack Count] -- 1.0 converts it to decimal and uses decimal division , CEILING(SUM(Quantity) * 1.0 / [Pack Items]) * [Pack Price] as [Packaging Cost] FROM ( -- Raw Polaroid data (basically we creating LookUp_Product and joining with orders) select JobDetail.OrderId, ProductName + ' ' + CONVERT(nvarchar(2), JobPDF.Pages / 2) as [Product Name] , job.Quantity , CASE when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 24 THEN 0.063 -- **** 12 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 48 THEN 0.063 -- **** 24 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 72 THEN 0.089 -- **** 36 pages ELSE NULL END as [Pack Price] ,CASE when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 24 THEN 2 -- **** 12 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 48 THEN 2 -- **** 24 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 72 THEN 3 -- **** 36 pages ELSE NULL END as [Pack Items] FROM Job WITH (NOLOCK) inner join LookUp_Product WITH (NOLOCK) on LookUp_Product.ProductID = job.ProductID inner join JobEvent WITH (NOLOCK) on JobEvent.JobID = job.JobID inner join JobDetail WITH (NOLOCK) on JobDetail.JobID = job.JobID inner join Orders WITH (NOLOCK) on Orders.OrderId = JobDetail.OrderID inner join JobPDF WITH (NOLOCK) on JobPDF.JobID = job.JobID where 1=1 AND LookUp_Product.ClientID = 'AC7EDBCD-39DF-45CE-8D96-A26EB17D1E2D' AND Job.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobDetail.CurrentEventID = 999999 AND JobEvent.EventID = 999999 AND JobEvent.DateDone >= @dateFrom AND JobEvent.DateDone < @dateTo AND PackPrice is not null AND PackPrice <> 0.0000 ) as Raw****Data GROUP BY Raw****Data.OrderID, [Product Name], [Pack Price], [Pack Items]) costPerOrder**** GROUP BY [Product Name], [Pack Price] ORDER BY [Product Name] END

Request execution example:

 declare @dateFrom DateTime = '2016-10-14' declare @dateTo DateTime = '2016-10-25' SELECT costPerOrder****.[Product Name], costPerOrder****.[Pack Price], SUM([Pack Count]) as [Pack Count], SUM(costPerOrder****.[Packaging Cost]) as [Packaging Cost] FROM ( -- Raw data aggregated by order SELECT [Product Name], [Pack Price] , CEILING(SUM(Quantity) * 1.0 / [Pack Items]) as [Pack Count] -- 1.0 converts it to decimal and uses decimal division , CEILING(SUM(Quantity) * 1.0 / [Pack Items]) * [Pack Price] as [Packaging Cost] FROM ( -- Raw **** data (basically we creating LookUp_Product and joining with orders) select JobDetail.OrderId, ProductName + ' ' + CONVERT(nvarchar(2), JobPDF.Pages / 2) as [Product Name] , job.Quantity , CASE when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 24 THEN 0.063 -- **** 12 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 48 THEN 0.063 -- **** 24 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 72 THEN 0.089 -- **** 36 pages ELSE NULL END as [Pack Price] ,CASE when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 24 THEN 2 -- **** 12 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 48 THEN 2 -- **** 24 pages when LookUp_Product.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobPDF.Pages = 72 THEN 3 -- ****36 pages ELSE NULL END as [Pack Items] FROM Job WITH (NOLOCK) inner join LookUp_Product WITH (NOLOCK) on LookUp_Product.ProductID = job.ProductID inner join JobEvent WITH (NOLOCK) on JobEvent.JobID = job.JobID inner join JobDetail WITH (NOLOCK) on JobDetail.JobID = job.JobID inner join Orders WITH (NOLOCK) on Orders.OrderId = JobDetail.OrderID inner join JobPDF WITH (NOLOCK) on JobPDF.JobID = job.JobID where 1=1 AND LookUp_Product.ClientID = 'AC7EDBCD-39DF-45CE-8D96-A26EB17D1E2D' AND Job.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobDetail.CurrentEventID = 999999 AND JobEvent.EventID = 999999 AND JobEvent.DateDone >= @dateFrom AND JobEvent.DateDone < @dateTo AND PackPrice is not null AND PackPrice <> 0.0000 ) as Raw****dData GROUP BY Raw****Data.OrderID, [Product Name], [Pack Price], [Pack Items]) costPerOrder**** GROUP BY [Product Name], [Pack Price] ORDER BY [Product Name]
+5
source share
1 answer

I think your data is more than what you found. Have you performed distinct in the entire column of JobPDF.Pages , and not just as a "representative"?

Since your query works with certain date ranges, and not with others, I would advise taking a range that doesn't, and looking at distinct return values, since your arithmetic overflow can only be because your query returns more than 2 characters after your separation.

The following is a description of the script, because even if there is data that will cause convert to fail, if it is not returned by the request in the first place, everything will work without problems:

 declare @a table (a int); insert into @a values(7155),(72); -- This query will run select CONVERT(nvarchar(2), a / 2) from @a where a < 100; -- This query will not select CONVERT(nvarchar(2), a / 2) from @a;

Quite simply, I don’t understand why there would be a problem other than the one above. Can you run the following and add the output to your question? If the dates do not correlate with the range that causes the error, replace the dates below:

 select distinct JobPDF.Pages FROM Job WITH (NOLOCK) inner join LookUp_Product WITH (NOLOCK) on LookUp_Product.ProductID = job.ProductID inner join JobEvent WITH (NOLOCK) on JobEvent.JobID = job.JobID inner join JobDetail WITH (NOLOCK) on JobDetail.JobID = job.JobID inner join Orders WITH (NOLOCK) on Orders.OrderId = JobDetail.OrderID inner join JobPDF WITH (NOLOCK) on JobPDF.JobID = job.JobID where 1=1 AND LookUp_Product.ClientID = 'AC7EDBCD-39DF-45CE-8D96-A26EB17D1E2D' AND Job.ProductID = 'AA5DF53B-4B58-4298-AFA3-EE856610689A' AND JobDetail.CurrentEventID = 999999 AND JobEvent.EventID = 999999 AND JobEvent.DateDone >= '20161014' AND JobEvent.DateDone < '20161025' AND PackPrice is not null AND PackPrice <> 0.0000
0
source

Source: https://habr.com/ru/post/1258820/

More articles:


All Articles