creating a search query - 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 2000 Forums

Transact-SQL (2000)

creating a search query

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-02-28 : 04:37:56

hi,
I am trying to create a query for a search screen.
I want to have one textbox where I can type in a keyword that could exisit in any of my tables fields, and select info from dropdown boxes aswell.
so far I have this but its not working...

@KW varchar(150),
@CL varchar(100),
@ST varchar(100),
@WS varchar(100),
@SN varchar(100)

AS

SELECT [Current Location], [Server Name], [Service Type], [Service Name], [workstream]
FROM Data
WHERE [current location] LIKE @CL and [Service Type] LIKE @ST and [Workstream] LIKE @WS and [Server Name] LIKE @SN
and
([Current Location] LIKE @KW and [Server Name] LIKE @KW and [Service Type] LIKE @KW and [workstream] LIKE @KW)
GO

I am using asp and my code to run the sp is :
Set RS = Conn.Execute("EXEC s_ViewInfo '%"& KW &"%', '"& CL &"%', '"& ST &"%', '"& WS &"%', '"& SN &"%' ")

anyone have any ideas why this is not working ?

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 05:23:42

you probably need % around your parameters:
LIKE '%' + @CL + '%'
and also you'd probably want to replace AND's for OR's.

Go with the flow & have fun! Else fight the flow

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-02-28 : 05:34:24

replace ands with ors ?
but I want to beable to type in a keyword and select something from the dropdown boxes....
I have % around my parameters. ( in my asp page I have %, so if one of the dropdowns or the keyword input box is blank it will ad a % )

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 05:43:13

these two coditions exclude them selves
where ([current location] LIKE @CL and [Service Type] LIKE @ST and [Workstream] LIKE @WS and [Server Name] LIKE @SN)
and -- change this to OR
([Current Location] LIKE @KW and [Server Name] LIKE @KW and [Service Type] LIKE @KW and [workstream] LIKE @KW)

Go with the flow & have fun! Else fight the flow

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-02-28 : 08:58:22

but how could I choose the server name (X) and type in a keyword (Y)?
by changing it to OR will it not bring back results for the server name = X and all the records where the keyword = Y
?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-28 : 09:14:31

the reason you're not getting anything back is this for Server Name only:
you have
[Server Name] LIKE @SN and [Server Name] LIKE @KW
so if [Server Name] LIKE @SN evaluates to true then [Server Name] LIKE @KW must also return true for the row to be returned.
as i see it this is nonsense, because unless the @SN and @KW are the same you wont get anything.
you could do:
([Server Name] LIKE @SN or [Server Name] LIKE @KW) which will evaluate to true if @SN or @KW match the condition.
if this isn't clear post table definitions, sample data and desired results so we can get a clear picture.

Go with the flow & have fun! Else fight the flow

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-02-28 : 09:40:45

ah, of course. doh !!
cheers ill test it out

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-03-04 : 08:23:50

hi, I still can';t get this to work..

SELECT [Current Location], [Server Name], [Service Type], [Service Name], [workstream]
FROM Data
WHERE ([current location] LIKE @CL or [current location] LIKE @KW)
and ([Service Type] LIKE @ST or [Service Type] LIKE @KW)
and ([Workstream] LIKE @WS or [Workstream] LIKE @KW)
and ([Server Name] LIKE @SN or [Server Name] LIKE @KW)

If I choose only a [server name] I would expect to see records where the servername = the chosen server name.
however i get all the results back from the table !

my ASP page passes this :
Set RS = Conn.Execute("EXEC s_ViewInfo '%"& KW &"%', '"& CL &"%', '"& ST &"%', '"& WS &"%', '"& SN &"%' ")

how can I modify my sql to show correct info ?

anthoer example, I would want to type a server name into the KW parameter and leave all the drop downs blank, the results expected would be only those where server name = the KW , but again all row fro mthe table are being returned at present.

thank you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-04 : 08:41:56

post some sample data. anything i'd say would be guessing and that's not helpfull....

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources