1

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
  • 4
    It doesn't. A plan is generated for both paths regardless of the passed in parameter. The only way to get this to work the way you expect it to is to have sub-stored procs called based on the parameter value. – Erik Darling Jul 26 '17 at 12:47
3

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.

enter image description here

enter image description here

enter image description here

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.