Yes, as mentioned in that article, I could able to avoid recompile, by utilising the OPTION (keepfixed plan), but my execution time is getting increased, when compared to before implementing...

Please find the Below Entire SP:

CREATE PROCEDURE dbo.prcCtlLnkSelFavCust( @userid int, @UserPartId int , @UserType int) AS

DECLARE @ErrMsg VARCHAR(500)

 ,@SPRETVAL int

 ,@RetVal int

 ,@ErrSpName varchar(100)

SELECT @ErrSpName = 'Stored procedure:' + OBJECT_NAME(@@PROCID)

Create table ...

Hi, Thanks for making me , Exploring this article crystal clear.. Now I got the Reason for my SP getting recompiled, But one...

Hi, Thanks for your Prompt reply. I have already gone thru this article at microsoft site, But I have some SP's having #temp tables INSERT queries like below :

INSERT #Temp...

Hi , Thanks for your Reply, but Iam creating the #temp table in the sp_Parent procedure, which is calling the sp_A stored Procedure.

The exact procedure is like below :

CREATE PROCEDURE...