question

In my new company, I saw stored procedure like below: CREATE PROCEDURE [dbo].[USP_INFO] ( @M_ID AS VARCHAR(20)=NULL ,@MS_ID AS numeric(3,0)=NULL ,@Source AS VARCHAR(100)=NULL ,@Type AS VARCHAR(100)=NULL ,@Spe AS VARCHAR(100)=NULL ,@Mem AS VARCHAR(100)=NULL ,@FName AS VARCHAR(100)=NULL ,@LName AS VARCHAR(100)=NULL ,@Reg AS VARCHAR(100)=NULL ,@Red AS VARCHAR(100)=NULL ,@St AS VARCHAR(100)=NULL ,@Cty AS VARCHAR(100)=NULL ) AS BEGIN DECLARE @Specs as varchar(110) SET @Specs = '%' + @Spe + '%' SELECT DISTINCT NAME ,V_ID ,[ST] ,Cty ,Category ,Spe ,SUB_Spe ,FNAME ,LNAME FROM dbo.USP_INFO_VW WHERE ((M_ID=coalesce(@M_ID, M_ID)) OR (M_ID is null and isnull(@M_ID,'')='')) AND ((MS_ID=COALESCE(@MS_ID,MS_ID)) or (MS_ID is null and isnull(@MS_ID,'')='')) AND ((CODE = COALESCE(@Source, CODE)) OR (CODE is null and isnull(@Source,'')='')) AND ((TYPE = COALESCE(@Type,TYPE)) or (TYPE is null AND ISNULL(@Type,'')='')) AND ((rtrim(ltrim(Spe)) like COALESCE(@Specs,rtrim(ltrim(Spe))) OR (Spe is null AND ISNULL(@Spe,'') = '')) OR (rtrim(ltrim(SUB_Spe)) like COALESCE(@Specs,rtrim(ltrim(SUB_Spe))) OR (SUB_Spe is null AND ISNULL(@Spe,'') = '' ))) AND ((Category = COALESCE(@Mem,Category)) OR (Category is null and isnull(@Mem,'')='')) AND ((FNAME like COALESCE(@FName, FNAME)) OR (FNAME is null and isnull(@FName,'')='')) AND ((LNAME like COALESCE(@LName, LNAME)) OR (LNAME is null and isnull(@LName,'')='')) AND ((GRANT = COALESCE(@Reg, GRANT)) OR (GRANT_RECIPIENT is null and isnull(@Reg,'')='')) AND ((RED = COALESCE(@Red, RED)) OR (RED is null and isnull(@Red,'')='')) AND (([ST]=COALESCE(@ST, [ST])) OR ([ST] is null and isnull(@ST,'')='')) AND ((Cty=COALESCE(@Cty, Cty)) OR (Cty is null and isnull(@Cty,'')='')) ORDER BY LNAME,FNAME END Can some expert help me to re-write it for better performance and using best practice? Thanks.