Search query problem - 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)

Search query problem

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-04 : 11:35:19

hi
iam working on an small serching ia have 3 fields suppleir,category,location so now user have option to fill any or all fields

Select suppliername from suppliers where suppliername like isnull('%@suupname%',suppliername) and category like isnull('%@category%',category) location like isnull('%@location%',location)

is tmy above query right and if some one know how I can write the same condition in access caz isnull is undefined function in access

JoeIngle
Starting Member

14 Posts

Posted - 2004-03-11 : 12:14:09

To answer your first question, this should work:

CREATE PROCEDURE PrSearchSuppliers
(
@SupplierName varchar(50) = NULL,
@Category varchar(50) = NULL,
@Location varchar(50) = NULL,
@Debug int = 0
)
AS

SET NOCOUNT ON

DECLARE @intErrorCode int
DECLARE @chvQuery varchar(8000)
DECLARE @chvWhere varchar(8000)

SELECT @intErrorCode = @@Error,
@chvQuery = 'SET QUOTED_IDENTIFIER OFF SELECT SupplierName FROM Suppliers ',
@chvWhere = ''

IF @intErrorCode = 0 AND @SupplierName IS NOT NULL
BEGIN
SET @chvWhere = @chvWhere + ' SupplierName LIKE "%' + CONVERT(varchar(50),@SupplierName) + '%" AND'
SELECT @intErrorCode = @@ERROR
END

IF @intErrorCode = 0 AND @Category IS NOT NULL
BEGIN
SET @chvWhere = @chvWhere + ' Category LIKE "%' + CONVERT(varchar(50),@Category) + '%" AND'
SELECT @intErrorCode = @@ERROR
END

IF @intErrorCode = 0 AND @Location IS NOT NULL
BEGIN
SET @chvWhere = @chvWhere + ' Location LIKE "%' + CONVERT(varchar(50),@Location) + '%" AND'
SELECT @intErrorCode = @@ERROR
END

IF @Debug <> 0 SELECT @chvWhere chvWhere

IF @intErrorCode = 0 AND SUBSTRING(@chvWhere, LEN(@chvWhere) - 3, 4) = ' AND'
BEGIN
SET @chvWhere = SUBSTRING(@chvWhere, 1, LEN(@chvWhere) - 3)
SELECT @intErrorCode = @@ERROR
IF @debug <> 0 SELECT @chvWhere chvWhere
END

IF @intErrorCode = 0 AND LEN(@chvWhere) > 0
BEGIN
SET @chvQuery = @chvQuery + ' WHERE ' + @chvWhere
SELECT @intErrorCode = @@ERROR
END

IF @Debug <> 0
SELECT @chvQuery Query

IF @intErrorCode = 0
BEGIN
EXEC (@chvQuery)
SELECT @intErrorCode = @@ERROR
END

RETURN @intErrorCode
GO

If you set your Debug param to 1 in QA you'll be able to see the query that's been built.

To use this in Access, and someone correct me if I'm wrong, you could substitute the IS NULL by using the LEN function, if it exists, I'm not up on Access. So:

If LEN(SupplierName) < 1 ..... your variable is empty

HTH

Joe

"He was a wise man who invented beer."

Plato

whitesword
Starting Member

17 Posts

Posted - 2004-03-11 : 18:32:32

I would highly recommend the following article:

"Dynamic Search Conditions in T-SQL" available at

[url]http://www.sommarskog.se/dyn-search.html[/url]

This covers various ways to search tables with dynamic conditions using Dynamic SQL and Static SQL (stored proced). Worth a good read especially if you are having performance problems like I was

Cheers
Roger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources