Different results from executing a stored procedure using exec sp_executesql compared to EXEC

Question

I have a table in the database which consists of some postcodes with their latitude and longitude for example:

Id  PostCode    Latitude         Longitude
1   ll29 8ht    53.2973289489746    -3.72970223426819

The table is generated by MVC code-first application, Latitude and longitude data type is real

I have a stored procedure that takes as input searchLatitude and searchLongitude 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 execute this through SQL server management studio, it executes the following:

DECLARE @return_value int

EXEC    @return_value = [dbo].[StockistSearch]
    @searchLat = 53.29,
    @searchLng = -3.72

SELECT  'Return Value' = @return_value

GO

Which return the distance correctly (0.645 miles)

However, when executed through the application using :

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

which according to a trace, executes:

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

which results in a completely different (and wrong) result for the calculated distance, (it returns distance 3688.96 miles!)

I've stepped through the code in debug and the parameters are being entered correctly and have confirmed that the exec sp_executesql is giving a different result that the EXEC when ran directly in the SQL server management studio

I'm completely at a loss as to why this is happening and although the stored procedure seems to be functioning correctly, the results showing through the application are completely wrong.

Please can someone please give me some advice before I go mad!!

Thanks

UPDATE

Thanks for all the comments so far and thanks to @McKay, I've narrowed it down to it ignoring the input parameters when called from exec sp_executesql and is using the defaults 0 for both latitude and longitude. If i remove the defaults, I get the error:

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

The SQL query is

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

This sql is automatically generate by the entity framework (code-first MVC EF doesn't currently support stored procedures but should be able to query stored procedures using the following in the DbContext:)

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

Would greatly appreciate any further help

FIXED :-)

When calling :

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

The command text has to have the parameters appended to it so

commandText = "StockistSearch @searchLat,@searchLng"

so the resulting sql is;

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

It now accepts the input parameters and calculates the correct distance

Thanks for the speedy comments, the stored procedure is part of a larger stored procedure and i've tried to strip it down to the basics to the part that's not returning the correct values, hopefully typos resolved now — samsinfield
I've updated this with all the original code. Everything is working great apart from the calculated distance — samsinfield
You know SQL Server now has a geography data type, right? And you should join tables using join, not where — podiluska
Is the ID of the returned result the same in both instances? — podiluska
There's probably just something confused, like a connection to the wrong procedure, database or server. Or connection options like ANSI_NULLS although they do not play a role in the code you posted. — usr

McKay McKay · Accepted Answer · 2013-08-16T14:29:15

Numbers that are this different make me think that maybe it's using the default values for something? Have you taken out the defaults?

Maybe you could also add the parameters to the select, to make sure you get what you're expecting.

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

This might aid in your debugging process.

Different results from executing a stored procedure using exec sp_executesql compared to EXEC

UPDATE

FIXED :-)

4 Answers