Obtaining Search Results - 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)

Obtaining Search Results

Author

Topic

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 15:47:05

I have a search page where the user can enter information into several textboxes that correspond to different database fields. I will use demographics as an example. The user can search by Name, Address, City, State, Zip, etc...

Well if the textbox is left blank then I do not want that field to be checked for in my WHERE clause. Currently blank textboxes pass NULL into the database (but they can pass whatever).

I was going to use dynamic SQL and EXEC(), but I'm worried about SQL Injection attacks. Is there a good way of going about this?

I'm thinking maybe something with an ISNULL() type of function. I'm having a little trouble piecing this together in my mind. Any help would be appreciated. Thank you.

Example:
Name = "Bob"
Zip = ""

SELECT *
FROM tblDemographics
WHERE Name = 'Bob'

but if

Name = "Bob"
Zip = "12345"

SELECT *
FROM tblDemograhpics
WHERE Name = 'Bob' and Zip = '12345'

And obviously with all the demographics they could search by I can't just have a huge list of IF statements

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 15:50:26

Dynamic WHERE clause without dynamic SQL:
http://www.sqlteam.com/item.asp?ItemID=2077

Tara Kizer
aka tduggan

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 15:55:35

I like it, thanks!!!

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 16:07:54

While my current project uses SQL Server, I also get assigned many Access projects. Do you know if any function similiar to that exists in Access or would I have to write a custom function to do that in Access? I think that is a really cool feature and would love to use it in my Access projects as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 16:14:39

I have never used Access, so I'm not sure.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 02:37:50

Istead of ISNULL or COALESCE you make use of IIF

COALESCE(condition,value1,value2) of SQL Server is equivalent to
IIF(condition,value1,value2) in Access

Madhivanan

Failing to plan is Planning to fail

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-21 : 08:42:39

Thanks madhivanan, good idea

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-21 : 10:36:23

Now I'm running into a problem with finding an easy way to incorporate wildcard searches into the search.

jhermiz

3564 Posts

Posted - 2006-02-21 : 11:21:42

Whats the exact issue?

WHERE (NAME LIKE '%' + @someName + '%') OR NAME=@someName

You can use boolean logic to allow for an OR condition to either take the right hand side or the left hand side

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-21 : 11:25:49

My current code looks like this (because there are several search fields and if the search field is left blank I do not want it searched so I pass NULL into the USP)

Name = COALESCE(@Name, Name)

jhermiz

3564 Posts

Posted - 2006-02-21 : 13:12:14

You could do that or :

WHERE
(@blah = someField OR @blah IS NULL)

I think I have something entitled Soup OR Salad AND A Meal in my blog: http://weblogs.sqlteam.com/jhermiz/archive/2005/12/22/8604.aspx

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-21 : 13:15:32

I realize that, I'm trying to incoporate a LIKE statement into that WHERE statement and have it not pick results it should not

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-28 : 17:38:31

Here is my current code. Is there a way to factor LIKE into the BrokerName field Something like:

WHERE BrokerName LIKE '%' + @BrokerName + '%'

But I still want the dynamic where part to work with all of the other fields (I may be adding more fields in the future too. Something with and without LIKE)


CREATE PROCEDURE dbo.usp_Search
(
	@AppNumber			int,
    @BrokerName         varchar(100),
    @TaxIdNumber        varchar(11)
)
AS

SELECT
	dbo.qryApplications.pkApplicationNumber,
	dbo.qryApplications.BrokerName,
	dbo.qryApplications.TaxIdNumber,
	dbo.tlkpStates.PostalAbbreviation,
	dbo.tlkpStatuses.Status,
	dbo.qryApplications.TimeSubmitted
FROM
	dbo.qryApplications
		LEFT OUTER JOIN	dbo.tlkpStates
			ON dbo.qryApplications.fkStateID = dbo.tlkpStates.pkStateID
		LEFT OUTER JOIN	dbo.tlkpStatuses
			ON dbo.qryApplications.fkStatusID = dbo.tlkpStatuses.pkStatusID
WHERE
	dbo.qryApplications.pkApplicationNumber = COALESCE(@AppNumber, dbo.qryApplications.pkApplicationNumber) AND
	dbo.qryApplications.BrokerName = COALESCE(@BrokerName, dbo.qryApplications.BrokerName) AND
	dbo.qryApplications.TaxIdNumber = COALESCE(@TaxIdNumber, dbo.qryApplications.TaxIdNumber)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 17:52:55

In order for us to help you, you need to provide DDL (CREATE TABLE) for all tables involved, sample data (in the form of INSERT INTO statements), and the expected result set using the sample data. Sample data does not have to be real data, but it does need to illustrate your problem. Once we have that, we can mimic your issue on our own SQL Servers.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-02-28 : 23:21:46

quote:

Originally posted by Billkamm

Here is my current code. Is there a way to factor LIKE into the BrokerName field Something like:

WHERE BrokerName LIKE '%' + @BrokerName + '%'

But I still want the dynamic where part to work with all of the other fields (I may be adding more fields in the future too. Something with and without LIKE)


CREATE PROCEDURE dbo.usp_Search
(
	@AppNumber			int,
    @BrokerName         varchar(100),
    @TaxIdNumber        varchar(11)
)
AS

SELECT
	dbo.qryApplications.pkApplicationNumber,
	dbo.qryApplications.BrokerName,
	dbo.qryApplications.TaxIdNumber,
	dbo.tlkpStates.PostalAbbreviation,
	dbo.tlkpStatuses.Status,
	dbo.qryApplications.TimeSubmitted
FROM
	dbo.qryApplications
		LEFT OUTER JOIN	dbo.tlkpStates
			ON dbo.qryApplications.fkStateID = dbo.tlkpStates.pkStateID
		LEFT OUTER JOIN	dbo.tlkpStatuses
			ON dbo.qryApplications.fkStatusID = dbo.tlkpStatuses.pkStatusID
WHERE
	dbo.qryApplications.pkApplicationNumber = COALESCE(@AppNumber, dbo.qryApplications.pkApplicationNumber) AND
	dbo.qryApplications.BrokerName = COALESCE(@BrokerName, dbo.qryApplications.BrokerName) AND
	dbo.qryApplications.TaxIdNumber = COALESCE(@TaxIdNumber, dbo.qryApplications.TaxIdNumber)

This is an issue more with boolean logic then with "how can I add a like query". If you want to incorporate a like query to execute WITH the additional where, and execute either side, then use the OR operator and tack on the LIKE condition. If you need both to be true use AND.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-02 : 08:36:43

jhermiz: I'm sorry I'm slightly confused by what you are telling me.

If @BrokerName is NULL is do not want @BrokerName included in my search. If it is NOT NULL then I want it included in my search as a LIKE. I'm having trouble accomplishing this in-line with the WHERE statement. I considered using and IF statement and having two queries (one where @BrokerName is NULL and one where it is not), but I decided that I may wish to have more fields use LIKE to search, so I was looking for a better way

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-02 : 08:42:27

tkizer:


CREATE TABLE [dbo].[qryApplications] (
	[pkApplicationNumber] [int] IDENTITY (1, 1) NOT NULL ,
	[TimeSubmitted] [datetime] NOT NULL ,
	[BrokerName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tlkpStates] (
	[pkStateID] [int] IDENTITY (1, 1) NOT NULL ,
	[PostalAbbreviation] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StateName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Available] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tlkpStatuses] (
	[pkStatusID] [int] IDENTITY (1, 1) NOT NULL ,
	[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-02 : 09:47:20

When @BrokerName is NULL would this cause the second line of logic to always be false? If so I believe this is my solution.

(
BrokerName = COALESCE(@BrokerName, qryApplications.BrokerName) OR
BrokerName LIKE '%' + @BrokerName + '%'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources