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
...............