vendredi 10 juillet 2020

How to optimize the stored procedure code

I have a stored Procedure which accepts 5 input parameters. The columns which the Stopred procedure returns is the same, but where condition changes according the params inputted. Currently I'm using if, else-if to check for different conditions, and select the statements according to where conditions. Can anyone help me with other optimized way?

Here is the piece of Stored Procedure

if(@OwnerWWID is null and @OwnerEmailID is null and @SourceURL is null and @ServerName is null and @Wave is not null)
    begin

            select distinct mp.Id,mp.SourceURL,.......
                    FROM dbo.MPlanner as mp with(nolock)
                    
                    where mp.MigrationWave=@Wave
    End
    else if(@OwnerWWID is not null and @OwnerEmailID is null and @SourceURL is null and @ServerName is null and @Wave is null)
    begin

        select distinct mp.Id,mp.SourceURL,.......
                FROM dbo.MPlanner as mp with(nolock)
                    where mp.OwnerWWID like @OwnerWWID+'%'
    End
    
    else if(@OwnerWWID is null and @OwnerEmailID is not null and @SourceURL is null and @ServerName is null and @Wave is null)
    begin

            select distinct mp.Id,mp.SourceURL,.......
                FROM dbo.MPlanner as mp with(nolock)
                    where ((mp.[Document Library Owner Email IDs] like @OwnerEmailID+'%' or mp.[Site Owner Email IDs] like @OwnerEmailID+'%' or mp.SourceURL like @OwnerEmailID+'%' or mp.UserEmailID like @OwnerEmailID+'%')and DestinationType='sp') or ((mp.OwnerEmailID like @OwnerEmailID+'%' or mp.SourceURL like @OwnerEmailID+'%' or mp.UserEmailID like @OwnerEmailID+'%') and mp.DestinationType='odfb' and mp.OwnerJJEDSStatus=1)
    End 

Aucun commentaire:

Enregistrer un commentaire