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

该表由MVC代码优先应用程序生成，纬度和经度数据类型是真实的

I have a stored procedure that takes as input searchLatitude and searchLongitude and then calculates the distances:

我有一个存储过程，它将searchLatitude和searchLongitude作为输入，然后计算距离：

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:

如果我通过SQL Server管理工作室执行此操作，它将执行以下操作：

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)

哪个正确返回距离（0.645英里）

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!)

这导致计算距离的结果完全不同（和错误），（返回距离3688.96英里！）

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

我在调试中逐步完成了代码并且正确输入了参数并确认了exec sp_executesql在SQL Server管理工作室中直接运行时给出了不同的结果

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:

感谢到目前为止的所有评论，感谢@McKay，我将其缩小到忽略输入参数时从exec sp_executesql调用并使用默认值0表示纬度和经度。如果我删除默认值，我收到错误：

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

The SQL query is

SQL查询是

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:)

这个sql是由实体框架自动生成的（代码优先的MVC EF目前不支持存储过程但应该能够使用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;

所以生成的sql是;

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

它现在接受输入参数并计算正确的距离

4 个解决方案

#1

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.

这可能有助于您的调试过程。

#2

Do you have two stored procedures, one called DistanceSearch and one called StockistSearch? It looks that way from your code. Are you sure the correct procedure is being called?

你有两个存储过程，一个叫做DistanceSearch，一个叫StockistSearch？它看起来就像你的代码。你确定正在调用正确的程序吗？

#3

Error in your proc.

你的过程出错了。

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

one uses @searchLat, one uses @searchLatitude

一个使用@searchLat，一个使用@searchLatitude

Wait, the proc seems to select tables, and not have a return value, but your execution is expecting an out parameter?

等等，proc似乎选择了表，并没有返回值，但是你的执行期望是一个out参数？

#4

The difference is in how it's called.

不同之处在于它的调用方式。

Your SSMS call returns the return value.

您的SSMS调用返回返回值。

Your sp_executesql call returns the dataset. I'm guessing the 40th closest stockist is 3688.96 miles away

您的sp_executesql调用返回数据集。我猜第40个最接近的库存商是3688.96英里远

#1