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.
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
gives incorrect results but
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