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