create table #artists ( id int )
if ( @showDeleted = 1 )
begin
insert into #artists select id from _artist where is_active = 1
end else if ( @showDeleted = 2 ) begin
insert into #artists select id from _artist where is_active = 0
end else begin
insert into #artists select id from _artist
end
if ( @fname is not null )
begin
delete from #artists where #artists.id not in (
select _artist.id from _artist where fname like '%'+ @fname +'%'
)
end
if ( @lname is not null )
begin
delete from #artists where #artists.id not in (
select _artist.id from _artist where lname like '%'+ @lname +'%'
)
end
if ( @keywords is not null )
begin
if ( @fname is null and @lname is null )
begin
delete from #artists where #artists.id not in (
select _artist.id from _artist
left join _photo on _photo.idArtist = _artist.id
where
_photo.title like '%'+ @keywords +'%'
or _photo.caption like '%'+ @keywords +'%'
or _photo.thumb_describe like '%'+ @keywords +'%'
or _photo.description like '%'+ @keywords +'%'
or _photo.additional_search like '%'+ @keywords +'%'
or _artist.fname like '%'+ @keywords +'%'
or _artist.lname like '%'+ @keywords +'%'
)
end else if ( @fname is not null ) begin
delete from #artists where #artists.id not in (
select _artist.id from _artist
left join _photo on _photo.idArtist = _artist.id
where
_photo.title like '%'+ @keywords +'%'
or _photo.caption like '%'+ @keywords +'%'
or _photo.thumb_describe like '%'+ @keywords +'%'
or _photo.description like '%'+ @keywords +'%'
or _photo.additional_search like '%'+ @keywords +'%'
or _artist.fname like '%'+ @keywords +'%'
)
end else if ( @lname is not null ) begin
delete from #artists where #artists.id not in (
select _artist.id from _artist
left join _photo on _photo.idArtist = _artist.id
where
_photo.title like '%'+ @keywords +'%'
or _photo.caption like '%'+ @keywords +'%'
or _photo.thumb_describe like '%'+ @keywords +'%'
or _photo.description like '%'+ @keywords +'%'
or _photo.additional_search like '%'+ @keywords +'%'
or _artist.lname like '%'+ @keywords +'%'
)
end
end