multiple search sp - SQL Server Forums

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)

multiple search sp

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 01:07:14

Hi.
I'm trying to do,currently thinking, how to do a multiple search.
It's like forums that have multiple search criteria.
So when a user get's to the search page he/she can input on one,two,three.....all the search fields.
It's only required to input on one field and the others can be left blank but the sp will include all the values, empty or not.

I was thinking on a

where user.id=@userid and
--minimum 1 field required
fieldname=@fieldname and (fieldname is null or fieldname is not null) and fieldname2=@fieldname2 and (fieldname2 is null or fieldname2 is not null)

etc...

1)Will it work?
2)Will it work if @fieldname comes back as null?
3)Will it work if @fieldname comes back as no null(for the specific value returned by @fieldname and don't bring back all nulls or not nulls also)
4)Any suggestions?

Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 01:40:34

quote:
Originally posted by sapator

Hi.
I'm trying to do,currently thinking, how to do a multiple search.
It's like forums that have multiple search criteria.
So when a user get's to the search page he/she can input on one,two,three.....all the search fields.
It's only required to input on one field and the others can be left blank but the sp will include all the values, empty or not.

I was thinking on a

where user.id=@userid and
--minimum 1 field required
fieldname=@fieldname and (fieldname is null or fieldname is not null) and fieldname2=@fieldname2 and (fieldname2 is null or fieldname2 is not null)
etc...

1)Will it work?
2)Will it work if @fieldname comes back as null?
3)Will it work if @fieldname comes back as no null(for the specific value returned by @fieldname and don't bring back all nulls or not nulls also)
4)Any suggestions?

Thanks.

Slight Changes

where user.id=@userid and
and (nullif(@fieldname,'') is null or fieldname=@fieldname)
and (nullif(@fieldname2,'') is null or fieldname2=@fieldname2)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 01:46:26

Hi.
Will have to give it a try tomorrow.
You have "and and (nullif( .......
The 2 "and" is a typo?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 01:51:13

Sure.

Ya its a typo.Thanks to point it out!

It should be!

where user.id=@userid
and (nullif(@fieldname,'') is null or fieldname=@fieldname)
and (nullif(@fieldname2,'') is null or fieldname2=@fieldname2)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 01:53:12

So this will find if it's null with the nullif(ans assign null) or if it's not null will assign the @fieldname vlaue to fieldname ?
Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 01:56:46

quote:
Originally posted by sapator

So this will find if it's null with the nullif(ans assign null) or if it's not null will assign the @fieldname vlaue to fieldname ?
Thanks.

Ya Exactly, but it also negate '' empty string too!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 02:10:46

"Ya Exactly, but it also negate '' empty string too!"
Mm sorry i did not understand that.
Empty string will be?Null?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 02:29:29

No,

Consider you procedure have 3 parameter @userid ,@fieldname,@fieldname2

exec procedure_name(100,null,null)
exec procedure_name(100,'',null)
exec procedure_name(100,null,'')

All the procedure call will return the same result!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 02:43:20

Aha.Ok.
But if you have a value that is '' and a value of null. It will consider them to be the same?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 02:48:07

No never! Both are entirely different

'' -> Empty String (applicable only for char and varchar datatypes)

Null -> Missing information and inapplicable information (Independent of datatypes)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-26 : 02:58:35

Good to know.
Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-26 : 03:00:48

quote:
Originally posted by sapator

Good to know.
Thanks.

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources