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