Various stored procedure execution results using exec sp_executesql compared to EXEC

Question

I have a table in a database that consists of some zip codes with their latitude and longitude, for example:

Id PostCode Latitude Longitude 1 ll29 8ht 53.2973289489746 -3.72970223426819 

The table is generated by the MVC code-first application. The latitude and longitude data type is real.

I have a stored procedure that takes Latitude and searchLongitude as an input search, and then calculates the distances:

 CREATE PROCEDURE [dbo].[StockistSearch] @searchLat float = 0, @searchLng float = 0 AS BEGIN SET NOCOUNT ON; SELECT top 40 s.Id as Id, a.Company as Company, a.FirstName as FirstName, a.LastName as LastName, a.Address1 as Address1, a.Address2 as Address2, a.City as City, a.ZipPostalCode as ZipPostalCode, a.PhoneNumber as PhoneNumber, a.FaxNumber as FaxNumber, a.Email as Email, s.latitude as Latitude, s.longitude as Longitude, ( 3959 * acos( cos( radians(@searchLat) ) * cos( radians( s.latitude ) ) * cos( radians( s.longitude ) - radians(@searchLng) ) + sin( radians(@searchLat) ) * sin( radians( s.latitude ) ) ) ) AS Distance FROM Stockist s, Customer c, Address a where s.CustomerId = c.Id and c.ShippingAddress_Id = a.Id ORDER BY distance END 

If I performed this through SQL Server Management Studio, it will do the following:

 DECLARE @return_value int EXEC @return_value = [dbo].[StockistSearch] @searchLat = 53.29, @searchLng = -3.72 SELECT 'Return Value' = @return_value GO 

Which correctly returns the distance (0.645 miles)

However, when executed through an application using:

 var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); 

which according to the trace performs:

 exec sp_executesql N'StockistSearch',N'@searchLat float,@searchLng float',@searchLat=53.29,@searchLng=-3.72 

which leads to a completely different (and incorrect) result for the calculated distance (it returns a distance of 3688.96 miles!)

I went through the code in debugging and the parameters were entered correctly and confirmed that exec sp_executesql gives a different result, which EXEC, when launched directly in the SQL server management studio

I am completely at a loss as to why this is happening, and although the stored procedure seems to work correctly, the results showing the application are completely wrong.

Please someone please give me advice before losing my mind!

thanks

UPDATE

Thanks for all the comments and thanks to @McKay, I narrowed it down to ignoring the input parameters when calling exec sp_executesql and uses the default value 0 for latitude and longitude. If I delete the defaults, I get an error:

 Procedure or function 'StockistSearch' expects parameter '@searchLat', which was not supplied. 

SQL query

 exec sp_executesql N'StockistSearch', N'@searchLat real, @searchLng real',@searchLat=53.29,@searchLng=-3.72 

This sql is automatically generated by the entity infrastructure (the first MVC MVC code currently does not support stored procedures, but should be able to query stored procedures using the following in DbContext :)

 var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); 

Thanks so much for any further help.

FIXED :-)

When called:

 var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); 

The command text must contain the parameters attached to it, therefore

 commandText = "StockistSearch @searchLat,@searchLng" 

so the resulting sql:

 exec sp_executesql N'StockistSearch @searchLat,@searchLng', N'@searchLat real, @searchLng real',@searchLat=53.29,@searchLng=-3.72 

Now it takes input parameters and calculates the correct distance

Answer 1

Numbers that differ from each other make me think that maybe he uses the default values ​​for something? Have you removed the default settings?

Perhaps you can also add options to the selection to make sure that you get what you expect.

 SELECT top 40 @searchLatitude, @searchLongitude, l.Id as Id, l.Postcode as Postcode, l.latitude as Latitude, l.longitude as Longitude, 

This can help in the debugging process.

Answer 2

Do you have two stored procedures, one called DistanceSearch and one called StockistSearch? This is similar to your code. Are you sure the correct procedure is being called?

Answer 3

Error in your program.

 ( 3959 * acos( cos( radians(@searchLatitude) ) * cos( radians( l.latitude ) ) * cos( radians( l.longitude ) - radians(@searchLongitude) ) + sin( radians(@searchLat) ) * sin( radians( l.latitude ) ) ) ) 

used @searchLat, used @searchLatitude

Wait, proc seems to select tables and has no return value, but is your execution expecting an output parameter?

Answer 4

The difference is in what it is called.

Your SSMS call returns a return value.

Your call to sp_executesql returns a dataset. My guess is that the 40th closest warehouse clerk is 3,688.96 miles away.