Show All record if no value pass

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

SQL Server 2005 Forums

Transact-SQL (2005)

Show All record if no value pass

Author

Topic

cool_moon
Starting Member

26 Posts

Posted - 2007-11-21 : 00:50:34

Hi

I have been trying to create a search form where user enter/select field values and in return they get data.

It's works fine if i select all the field but if i don't select any field it should return all the values.

example:
If we have 3 fields

Movies | Director | Stars | Genre

====================== SP where condition ======================
WHERE
AND
Program.Movies = ' + @Movies +')
AND
Program.Genre ='+@Genre+'
AND
Program.Director = '+ @Director +'
AND
Program.Star = '+ @Star +'

======================================================================

If i select @Director fields then it filter the record and shows me all the record of selected director, but if i don't select @Director then by default it should show me movies of all the directors, which is not happening at the moment.Same issue with other field also i.e. @Start,@Movies,@Genre also.

Please helpppp.........!!!

Thanks

Nabeel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 01:03:23

[code]
AND (
@Star is null
or Program.Star = @Star
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-21 : 01:04:06

Change like this:-

WHERE
AND
Program.Movies = ' + @Movies +'OR @Movies IS NULL )
AND
Program.Genre ='+@Genre+'OR @Genre IS NULL
AND
Program.Director = '+ @Director +'OR @Director IS NULL
AND
Program.Star = '+ @Star +'OR @Star IS NULL...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-21 : 01:05:24

use the template below. What this is saying to the query is if the value movies is null, then don't use it as a filter.

AND
Program.Movies = coalesce(@Movies,Program.Movies)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 01:08:52

quote:
Originally posted by visakh16

Change like this:-

WHERE
AND (
Program.Movies = ' + @Movies +'OR @Movies IS NULL )
AND (
Program.Genre ='+@Genre+'OR @Genre IS NULL )
AND (
Program.Director = '+ @Director +'OR @Director IS NULL )
AND (
Program.Star = '+ @Star +'OR @Star IS NULL )

KH
[spoiler]Time is always against us[/spoiler]

cool_moon
Starting Member

26 Posts

Posted - 2007-11-22 : 02:13:11

Thank alot guys....it's working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources