How to search with 9 fields in a single query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to search with 9 fields in a single query?

Author

Topic

Ambikaa
Starting Member

43 Posts

Posted - 2008-08-23 : 04:01:11

Hi,

i have 9 fields in a table name tbl_prty and the fields are keyword, prid,category,state,city,zip,price,features and mortgage.How to do the search of these 9 fields in a single table in a single query?
I may select any one of the fields combination like
keyword, mortgage
keyword
price......

Regards,
Ambika.K

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 04:42:17

[code]
select *
from tbl_prty
where (
@keyword is null
or (@keyword is not null and keyword = @keyword)
)
and (
@prid is null
or (@prid is not null and prid= @prid)
)
. . .
[/code]

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

tishri
Yak Posting Veteran

95 Posts

Posted - 2008-08-23 : 05:11:21

how about this?

select *
from tbl_prty
where (
@keyword is null
or keyword = @keyword
)
and (
@prid is null
or prid= @prid
)

TCC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 05:19:17

quote:

Originally posted by khtan


select *
from   tbl_prty
where  (
            @keyword is null
       or   (@keyword is not null and keyword = @keyword)
       )
and    (
            @prid is null
       or   (@prid is not null and prid= @prid)
       )
. . .

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

Arent these condition checks redundant. wont this be enough?
(@keyword is null
or keyword = @keyword)...

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-23 : 05:31:23

this query is not getting correct output

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 05:46:40

quote:
Originally posted by vidhya

this query is not getting correct output

You are not providing enough information for us to help you.

Post your table structure in CREATE TABLE ... syntax,
Sample data in INSERT INTO ... syntax
and the result that you need

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 05:47:02

quote:
Originally posted by vidhya

this query is not getting correct output

why?whts the error?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 05:53:00

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

select *
from tbl_prty
where (
@keyword is null
or (@keyword is not null and keyword = @keyword)
)
and (
@prid is null
or (@prid is not null and prid= @prid)
)
. . .

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

Arent these condition checks redundant. wont this be enough?
(@keyword is null
or keyword = @keyword)...

It is. Can't remember the actual situation, i once had a complex query with numerous parameters and the performance was improve with the addition of "@para IS NOT NULL" statement. And the query plan for with and without that statement is much faster than without it.

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

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-23 : 05:56:20

create procedure pname( @stateid bigint,@prty_id bigint) as
select *
from tbl_property
where (
@stateid is null
or (@stateid is not null and stateid = @stateid)
)
and (
@prty_id is null
or (@prty_id is not null and prty_id= @prty_id)
)

This is my query that i executed.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 05:59:08

quote:
Originally posted by vidhya

this query is not getting correct output

are you Ambikaa also ?

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

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-23 : 06:06:39

no, i need same result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 06:11:49

quote:
Originally posted by vidhya

no, i need same result.

Then you better start a new thread with your question and not to confuse with this.
1. Post your table DDL in terms of CREATE TABLE . . .
2. Sample Data in terms of INSET INTO . . .
3. Result Required using [ code ] tag (without spaces)

see http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 06:25:23

It is. Can't remember the actual situation, i once had a complex query with numerous parameters and the performance was improve with the addition of "@para IS NOT NULL" statement. And the query plan for with and without that statement is much faster than without it.

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

[/quote]
Oh is it? Thats something i'vent noticed. Thanks for the hint.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 06:31:23

quote:
Originally posted by visakh16

It is. Can't remember the actual situation, i once had a complex query with numerous parameters and the performance was improve with the addition of "@para IS NOT NULL" statement. And the query plan for with and without that statement is much faster than without it.

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

Oh is it? Thats something i'vent noticed. Thanks for the hint.

[/quote]

I didn't have time to investigate further the actual cause at that time. Since it is fixed, I just leave it as it is at the time and wanna revisit that when i have time. Guess I am still don't have time

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources