SQL Server 2008：一个存储过程中的多个查询

SQL Server 2008 : Multiple Queries in One Stored Procedure

提问by Moses Machua

I have 2 stored procedures that retrieve data from a table called Places. The procedures are called by C# code one at a time. These are the table columns.

我有 2 个存储过程，它们从名为Places. 这些过程由 C# 代码一次调用一个。这些是表列。

  [ID] int NOT NULL IDENTITY(1, 1),
  [Name] varchar(150) NOT NULL,
  [Latitude] decimal(18, 2) NOT NULL,
  [Longitude] decimal(18, 2) NOT NULL

These are the 2 stored procedures

这是2个存储过程

CREATE procedure dbo.GetPlacesByID
    @ID int
AS
SELECT *
FROM dbo.Places
WHERE  ID = @ID
GO 

and

和

CREATE procedure dbo.GetNearbyPlaces
    @Min_Lat decimal(18, 2),
    @Min_Lng decimal(18, 2),
    @Max_Lat decimal(18, 2),
    @Max_Lng decimal(18, 2)
AS
SELECT *
FROM dbo.Places 
WHERE Latitude BETWEEN @Min_Lat AND @Max_Lat
AND   Longitude BETWEEN @Min_Lng AND @Max_Lng
ORDER By ID ASC
GO

The C# application calls GetPlacesByIDfirst. If a row is returned, the app takes the latitude and longitude data and calculates the @max_Lng, @Min_Lng, @Max_Latand @Max_Longvariables by adding or subtracting a constant 0.005. These variables are passed to the GetNearbyPlacessp which returns nearby places and the app displays the original place plus the nearby places on a Google map.

C# 应用程序GetPlacesByID首先调用。如果返回的行，应用程序需要的纬度和经度数据，并计算@max_Lng，@Min_Lng，@Max_Lat和@Max_Long通过加上或减去一个常数0.005变量。这些变量被传递给GetNearbyPlaces返回附近地点的sp，应用程序会在 Google 地图上显示原始地点和附近地点。

This works fine but there are two round trips to the database which is not very efficient. I would like to combine the two procedures into one with something like

这工作正常，但有两次到数据库的往返，效率不高。我想将这两个程序合二为一

create procedure dbo.GetPlaces
  @ID int
select * from  dbo.Places as Row1
where ID=@ID
if Row1 is not null
  Declare @Min_Lat decimal(18, 2),
  Declare @Min_Lng decimal(18, 2),
  Declare @Max_Lat decimal(18, 2),
  Declare @Max_Lng decimal(18, 2)

  Set @Min_Lat=Row1.Latitude - 0.005
  Set @Min_Lng=Row1.Longitude - 0.005
  Set @Max_Lat=Row1.Latitude + 0.005
  Set @Max_Lng=Row1.Longitude + 0.005

 select * from dbo.Places
 where Latitude BETWEEN @Min_Lat AND @Max_Lat
 and   Longitude BETWEEN @Min_Lng AND @Max_Lng
 ....

The procedure would return the original row(Row1) + all the nearby places. I'm looking for suggestions on how to accomplish this. Thanks.

该过程将返回原始行(Row1) + 所有附近的地方。我正在寻找有关如何实现这一目标的建议。谢谢。

回答by sgeddes

Give this a try:

试试这个：

CREATE procedure GetNearbyPlaces
  @Id int
AS
BEGIN
  Declare @Min_Lat decimal(18, 3)
  Declare @Min_Lng decimal(18, 3)
  Declare @Max_Lat decimal(18, 3)
  Declare @Max_Lng decimal(18, 3)

  SELECT @Min_Lat=Latitude - 0.005,
    @Min_Lng=Longitude - 0.005,
    @Max_Lat=Latitude + 0.005,
    @Max_Lng=Longitude + 0.005
  FROM Places
  WHERE Id = @Id

  SELECT *
  FROM Places 
  WHERE Latitude BETWEEN @Min_Lat AND @Max_Lat
  AND   Longitude BETWEEN @Min_Lng AND @Max_Lng
  ORDER By ID ASC

END
GO

• SQL Fiddle Demo

• SQL 小提琴演示

---

---

After relooking this over, another issue you could potentially be having is with DECIMAL(18,2) -- I think this needs to be DECIMAL(18,3) since you're offsetting with .005.

重新审视这一点后，您可能遇到的另一个问题是 DECIMAL(18,2)——我认为这需要是 DECIMAL(18,3)，因为您用 0.005 抵消。

Here is a simpler version with a single sql statement:

这是一个带有单个 sql 语句的更简单版本：

SELECT P.*
  FROM Places P
    JOIN (
      SELECT Latitude - 0.005 Min_Lat,
        Longitude - 0.005 Min_Lng,
        Latitude + 0.005 Max_Lat,
        Longitude + 0.005 Max_Lng
      FROM Places
      WHERE Id = @Id
      ) P2 ON P.Latitude BETWEEN P2.Min_Lat AND P2.Max_Lat
          AND P.Longitude BETWEEN P2.Min_Lng AND P2.Max_Lng
  ORDER By ID ASC

• Updated Fiddle

• 更新的小提琴