create procedure sp_searchArtists
(
@keywords varchar(1000) = null ,
@fname varchar(150) = null ,
@lname varchar(150) = null ,
@showDeleted int = 3 ,
@orderBy varchar(100) = 'lname asc'
)
as
set nocount on
begin
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
if ( @orderBy = 'fname asc' )
begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.fname asc
end else if ( @orderBy = 'fname desc' ) begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.fname desc
end else if ( @orderBy = 'lname asc' ) begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.lname asc
end else if ( @orderBy = 'lname desc' ) begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.lname desc
end else if ( @orderBy = 'display_name asc' ) begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.display_name asc
end else if ( @orderBy = 'display_name desc' ) begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.display_name desc
end else begin
select _artist.id , _artist.fname , _artist.lname , _artist.display_name , _artist.is_active , _photo.id as idPhoto ,
_photo.thumb_image , _photo.thumb_width , _photo.thumb_height
from _artist
left join _photo on _photo.id = (
select top 1 idPhoto from _artist_detail_photo where idArtist = _artist.id order by newid() desc
)
where _artist.id in ( select #artists.id from #artists )
order by _artist.lname asc
end
drop table #artists
end
go