Conditionally Filtering An Integer Field

Question

Post reply

Conditionally Filtering An Integer Field

dec_obrien

Default port

Points: 1486
More actions
July 5, 2010 at 9:06 am

#139626

Hi,
I have a search function that passes some search criteria in parameters to a stored procedure. I can do this when each parameter contains a value to search on but I run into a problem where I only want to search of some of the criteria:
ALTER PROCEDURE dbo.SearchCompany(
@SupplierNo varchar(20) = '',
@Region varchar(100),
@Entity varchar(100),
@Category int,
@Service int,
@ApprovalStatus varchar(30)
)
So if I don't want to filter on @Category or @Service, what do I do? I have also included my WHERE section (without the filter for the int fields)
WHEREtblCompany.Active = 1 AND
tblEntCompany.FinanceID LIKE
CASE
WHEN @SupplierNo= '' THEN '%'
WHEN @SupplierNo IS NULL THEN '%'
ELSE @SupplierNo
END
I can't use the CASE block above for an int field so how do I get around it?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would use dynamic sql and sp_executesql.

Here's a great article on how to use dynamic search conditions based on the parameters passed to the procedure:

http://www.sommarskog.se/dyn-search-2005.html#sp_executesql

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dec_obrien Default port Points: 1486 More actions · Answer 3

Hi,

I didn't really want to use dynamic SQL because of the risk of SQl Injection (it's for an internet site). I have settled on this:

(@Category = 0 OR @Category = tblServCompany.FK_CategoryID) AND

.etc...

To use this, if the parameter is not being used, I set it = 0

Thanks for the help.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

If you use sp_executesql sql injection is not an issue.

I don't see any problems.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

dec_obrien (7/5/2010)
Hi,
I didn't really want to use dynamic SQL because of the risk of SQl Injection (it's for an internet site). I have settled on this:
(@Category = 0 OR @Category = tblServCompany.FK_CategoryID) AND
.etc...
To use this, if the parameter is not being used, I set it = 0

I hope that good performance is not a requirement here, because you will not have it on larger row counts with this and the CASE WHEN.. structure that you're using on the strings.

You're very likely to get full table scans regardless of what indexes you have.

Parametrize the queries properly, don't concatenate any user input into the string and use sp_executesql, and there's no chance of SQL injection.

http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dec_obrien Default port Points: 1486 More actions · Answer 6

The tables will not contain that many rows (<5000) but I'd be concerned if performance was slow or if I use this logic on larger tables. If I changed the CASE statement to

WHERE (@SupplierCode IS NULL OR tblEntCompany.FinanceID = @SupplierCode)

would this be more efficient?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

I take it you didn't read the link I gave you...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dec_obrien Default port Points: 1486 More actions · Answer 8

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

This one? Yes, I did; well, pardon the pun, I "scanned" it rather than reading slowly and nothing on performance of IS NULL jumped out at me.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Then I suggest you read it instead of scanning quickly, because the majority of the post is on the performance characteristics of queries using the (@Parameter IS NULL Or Column=@Parameter) predicate form.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass