Skip to main content
added [query-performance] to 2412 questions - Shog9 (Id=1924)
Link
added 3 more tags
Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311
Tweeted twitter.com/#!/StackDBAs/status/626853464715476992
Source Link
sebeid
  • 1.4k
  • 2
  • 17
  • 28

Stored procedure with recompile

I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params and we wanted the optimizer to grab a new plan with each call (not ideal, but it was an attempt to get it to run more reliably faster each time vs. using an old cached plan)"

Is what they saying correct ? Is there any way they can do that with no recompile

   create PROCEDURE [dbo].[VERIFIER_QUEUE]
   @cas_name varchar(20) = NULL,
   @instance_name varchar(50) = NULL,
   @verifier_id int = NULL,
   @applicant_type VARCHAR(20) = NULL

    WITH RECOMPILE     
    AS

   BEGIN 
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............