What happens when a stored procedure executes one of two different queries based on an input parameter? I have encountered a stored procedure that performs one of two different queries based on an input parameter.  Does this remove the plan from cache and force the query plan to be recompiled if the input parameter changes?  What are the performance implications of implementing control flow in this way.

Example:
CREATE PROCEDURE ControlledFlow 
    @Flow bit   
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT ColA FROM TableA
 END
ELSE
 BEGIN
  SELECT ColB FROM TableB
 END
END
To explain what Erik mentioned I created a stored procedure using your example.

--Creating table

CREATE TABLE [dbo].[Hotel](
    [HotelID] [int] NOT NULL,
    [HotelName] [nvarchar](50) NULL,
    [Rating] [nvarchar](10) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--Creating stored procedure
CREATE PROCEDURE ControlledFlow 
    @Flow bit   
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT HotelID FROM Hotel
 END
ELSE
 BEGIN
  SELECT HotelName FROM Hotel
 END
END


As soon as you make the first call (irrespective of what value you pass for @Flow parameter) you get a plan for both paths. Below pictures shows both path.







In order to have separate plan for @Flow value you will need something like this.

CREATE PROCEDURE ControlledFlow 
    @Flow bit   
AS
BEGIN
IF (@Flow =1)
 BEGIN
  EXECUTE SubProcedure1
 END
ELSE
 BEGIN
  EXECUTE SubProcedure1
 END
END