title: SQL Server 2008 : Multiple Queries in One Stored Procedure
body: <p>I have 2 stored procedures that retrieve data from a table called <code>Places</code>. The procedures are called by C# code one at a time. These are the table columns.</p>
  
  <pre><code>  [ID] int NOT NULL IDENTITY(1, 1),
    [Name] varchar(150) NOT NULL,
    [Latitude] decimal(18, 2) NOT NULL,
    [Longitude] decimal(18, 2) NOT NULL
  </code></pre>
  
  <p>These are the 2 stored procedures</p>
  
  <pre><code>CREATE procedure dbo.GetPlacesByID
      @ID int
  AS
  SELECT *
  FROM dbo.Places
  WHERE  ID = @ID
  GO 
  </code></pre>
  
  <p>and</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The C# application calls <code>GetPlacesByID</code> first. If a row is returned, the app takes the latitude and longitude data and calculates the <code>@max_Lng</code>, <code>@Min_Lng</code>, <code>@Max_Lat</code> and <code>@Max_Long</code> variables by adding or subtracting a constant 0.005. These variables are passed to the <code>GetNearbyPlaces</code> sp which returns nearby places and the app displays the original place plus the nearby places on a Google map. </p>
  
  <p>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</p>
  
  <pre><code>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
   ....
  </code></pre>
  
  <p>The procedure would return the original row(Row1) + all the nearby places. I'm looking for suggestions on how to accomplish this. Thanks.</p>