Display no records if parameters are empty

[Bill Obby]

I have a stored procedure which accepts up to four optional parameters. This works fine, but if all the text boxes which (feed the parameters)are empty then I get all records displayed which I do not want.

ALTER PROCEDURE [dbo].[sp_threekeywordsearch]
@keyword1 VARCHAR(255) = null,
@keyword2 VARCHAR(255) = null ,
@keyword3 VARCHAR(255) = null,
@composer varchar(255) = null


AS
SELECT description, TrackTitle,CDTitle,Source
FROM dbo.NON_STOP_PLAYLIST
where   (keywords like '%' + @keyword1 + '%' or @keyword1 IS NULL)  
    and  
    (keywords like '%' + @keyword2 + '%' or @keyword2 IS NULL)  
and  
    (keywords like '%' + @keyword3 + '%' or @keyword3 IS NULL)  and
    (composer like '%' + @composer + '%' or @composer IS NULL)

[NeoPa]

As the logic you wish to apply for all NULLs is the opposite of the logic used for NULLs when any keywords are present I suggest you will need to use CASE to handle all NULLs as a special situation.

[Bill Obby]

Hi NeoPa,

Do you have a simple example of the syntax for this ?

Bill

[sandeep M]

Dear Frnd...

this may help you..

if(@keyword1 is null and @keyword2 is null  and  @keyword3=null and @composer is null)
	SELECT description, TrackTitle,CDTitle,Source FROM dbo.NON_STOP_PLAYLIST where 1<>1
else
SELECT description, TrackTitle,CDTitle,Source 
FROM dbo.NON_STOP_PLAYLIST 
where   (keywords like '%' + @keyword1 + '%' or @keyword1 IS NULL)   
    and   
    (keywords like '%' + @keyword2 + '%' or @keyword2 IS NULL)   
and   
    (keywords like '%' + @keyword3 + '%' or @keyword3 IS NULL)  and 
    (composer like '%' + @composer + '%' or @composer IS NULL)

[NeoPa]

CASE (Transact-SQL) gives you examples as well as the full syntax specification.

[Sandeep M]

its very bad.. not posting reply, if someone posted an answer found worked or not .... I'm leaving out from this forum.. I feel so bad

[MMcCarthy]

Sandeep your post has been displayed. The moderation queue can be a little slow sometimes. You can avoid this by registering as a member.