Question :
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
Answer :
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