It Took Me Ages To Figure This One Out: A Puzzle

July 3, 2013

It Took Me Ages To Figure This One Out: A Puzzle

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 12:00 pm

Takeaway: I tackled a problem recently and I present a version of the problem here as a challenge. The challenge is to figure out why a query and a stored procedure return different results even though the procedure is defined using the same query.

If no one cracks this one, I’ll leave the answer in the comments in a couple days.

The Setup

Okay, I use AdventureWorks2012 here. I define a table data type IDList and a stored procedure s_GetCustomerProductPurchases:

use Adventureworks2012
go
 
create type IDList as TABLE(id int);
go
 
CREATE PROCEDURE dbo.s_GetCustomerProductPurchases 
	@CustomerIds IDList READONLY,
	@ProductIds IDList READONLY,
	@SearchString NVARCHAR(1000)
AS
SET NOCOUNT ON 
 
DECLARE @ResultList TABLE ( 
	ProductId INT, 
	ProductName Name 
	);
 
insert @ResultList (ProductId, ProductName)
select distinct TOP (2000) p.ProductID, p.Name
from sales.salesorderdetail sod
join sales.salesorderheader soh 
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product p
    on p.ProductID = sod.ProductID
join Sales.Customer c
    on c.CustomerID = soh.CustomerID
join @CustomerIds CIds
    on CIds.id = c.CustomerID
join @ProductIds PIds
	on PIds.id = p.ProductID
where p.Name like '%' + @SearchString + '%'
	and soh.[Status] = 5
 
IF	@@ROWCOUNT = 0 
	SELECT	0 ProductId, 'No Matching Products' ProductName
ELSE 
	SELECT	 ProductId, ProductName
	FROM	 @ResultList 
	ORDER BY ProductId

Executing the Query

Next lets run a query against Adventureworks2012. We define some variables that are used as filters in a query. This query populates a temporary results table and then we select from it:

DECLARE 
	@ProductIds IDList,
	@CustomerIds IDList,
	@SearchString Name = 'Red';
insert @ProductIds (id) VALUES (707), (717), (727)
insert @CustomerIds (id) VALUES (11000), (11103), (11201)
 
SET NOCOUNT ON 
 
DECLARE @ResultList TABLE ( 
	ProductId INT, 
	ProductName Name 
	);
 
insert @ResultList (ProductId, ProductName)
select distinct TOP (2000) p.ProductId, p.Name
from sales.salesorderdetail sod
join sales.salesorderheader soh 
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product p
    on p.ProductID = sod.ProductID
join Sales.Customer c
    on c.CustomerID = soh.CustomerID
join @CustomerIds CIds
    on CIds.id = c.CustomerID
join @ProductIds PIds
	on PIds.id = p.ProductID
where p.Name like '%' + @SearchString + '%'
	and soh.[Status] = 5
 
IF	@@ROWCOUNT = 0 
	SELECT	0 ProductId, 'No Matching Products' ProductName
ELSE 
	SELECT	 ProductId, ProductName
	FROM	 @ResultList 
	ORDER BY ProductId

These are the results I get:

ProductId	ProductName
707	Sport-100 Helmet, Red

Executing the Procedure

Now run the procedure which contains the exact same query:

DECLARE 
	@ProductIds IDList,
	@CustomerIds IDList,
	@SearchString Name = 'Red';
insert @ProductIds (id) VALUES (707), (717), (727)
insert @CustomerIds (id) VALUES (11000), (11103), (11201)
 
exec dbo.s_GetCustomerProductPurchases @ProductIds, @CustomerIds, @SearchString

The results we get now look like:

ProductId	ProductName
0	No Matching Products

So Why Are These Results Different?

That’s what I want you to figure out. Try to crack this one before looking at the comments.

SQL Fiddle

Have you used SQL Fiddle yet? It’s a site that allows SQL developers to set up different scenarios for use in online discussions. So if you don’t have Adventureworks2012 handy, I’ve setup up two SQLFiddles here and here. Your job then is to follow these two links and explain why the results are different.

-- Comments (7)

7 Comments »

You’ve reversed the order when passing in the CustomerIDs and ProductIDs types as parameters into the procedure.

Comment by Dan (@sql3d) — July 3, 2013 @ 1:46 pm
Haha, the hardest problem in the world – the typo!

Comment by James Lean — July 4, 2013 @ 5:21 am
Absolutely Dan.

In our real life case, getting from “We’ve got unexpected results” to “there’s a discrepancy between old query behavior and new sproc behavior” took some time as well.

The lesson we’re taking from this typo is to name your parameters. And so
exec dbo.s_GetCustomerProductPurchases @ProductIds, @CustomerIds, @SearchString
exec dbo.s_GetCustomerProductPurchases @ProductIds, @CustomerIds, @SearchString
gives incorrect results but
exec dbo.s_GetCustomerProductPurchases @ProductIds = @ProductIds, @CustomerIds = @CustomerIds, @SearchString = @SearchString
exec dbo.s_GetCustomerProductPurchases @ProductIds = @ProductIds, @CustomerIds = @CustomerIds, @SearchString = @SearchString
gives correct results.

Comment by Michael J. Swart — July 4, 2013 @ 8:00 am
Aaron Bertrand (b|t) pointed me to a post (First steps into Hekaton) by Dave Ballantyne (b|t) who suggests that in SQL Server 2014 (Hekaton), when you pass parameters to a natively compiled stored procedure, the named parameters might be slow. This guess is based on a mysterious new extended event: “hekaton_slow_parameter_passing”. Go read Dave’s article.

Comment by Michael J. Swart — July 4, 2013 @ 8:11 am
it’s a very nice post solution was so simple u passing invalid arguments that’s why you got different result in different query let give short answer both queries are different that’s why u got different result

let me explain throw query

REATE PROCEDURE dbo.s_GetCustomerProductPurchases @CustomerIds IDList READONLY, @ProductIds IDList READONLY,@SearchString NVARCHAR(1000)

— definition of sp

exec dbo.s_GetCustomerProductPurchases @ProductIds, @CustomerIds, @SearchString –:Execustion of sp

you are passing wrong argument ( @ProductIds instead of @CustomerIds )

Comment by Sohaib Ali — July 9, 2013 @ 2:00 am
Just a nit to pick. My understanding is that Hekaton is not the codename for SQL Server 2014. It’s just for project that created the in-memory technology features introduced in SQL Server 2014.

Comment by Gil — July 18, 2013 @ 2:22 pm
Yeah, I guess I can see how one could infer that I implied the codename for SQL Server 2014 was Hekaton. But it’s precisely those Hekaton features that Dave Ballantyne and I were writing about. I guess to be clearer, I should have written “SQL Server 2014’s Hekaton”

Comment by Michael J. Swart — July 18, 2013 @ 2:35 pm