Return no records when parameters are empty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return no records when parameters are empty

Author

Topic

Bill Obby
Starting Member

2 Posts

Posted - 2010-11-05 : 08:23:54

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.




   1. ALTER PROCEDURE [dbo].[sp_threekeywordsearch]
   2. @keyword1 VARCHAR(255) = null,
   3. @keyword2 VARCHAR(255) = null ,
   4. @keyword3 VARCHAR(255) = null,
   5. @composer varchar(255) = null
   6.  
   7.  
   8. AS
   9. SELECT description, TrackTitle,CDTitle,Source
  10. FROM dbo.NON_STOP_PLAYLIST
  11. where   (keywords like '%' + @keyword1 + '%' or @keyword1 IS NULL)  
  12.     and  
  13.     (keywords like '%' + @keyword2 + '%' or @keyword2 IS NULL)  
  14. and  
  15.     (keywords like '%' + @keyword3 + '%' or @keyword3 IS NULL)  and
  16.     (composer like '%' + @composer + '%' or @composer IS NULL)

Devart
Posting Yak Master

102 Posts

Posted - 2010-11-05 : 08:35:16

Hello,

For example:

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

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 08:38:35

Maybe this:

SELECT description, TrackTitle,CDTitle,Source
FROM dbo.NON_STOP_PLAYLIST
where   COALESCE(@keyword1,@keyword2,@keyword2,@composer,'noparms') <> 'noparms'
and (
	(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)  
	)

edit: formatting

No, you're never too old to Yak'n'Roll if you're too young to die.

Bill Obby
Starting Member

2 Posts

Posted - 2010-11-05 : 11:12:06

thanks chaps for this - webfred's solution worked for me because I wanted to filter the data by addition of keywords instead of an or.
i.e Devart's solution exec [dbo].[sp_threekeywordsearch] 'jazz','violin' would give me results with jazz or violin whereas I need jazz AND violin.

Best Wishes

Bill

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 11:16:33

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-05 : 12:18:24

ouch...

scan city

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources