July 5, 2010 at 9:06 am
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)
WHERE tblCompany.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
July 5, 2010 at 9:22 am
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
July 5, 2010 at 9:23 am
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
July 5, 2010 at 10:08 am
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.
July 5, 2010 at 10:12 am
If you use sp_executesql sql injection is not an issue.
I don't see any problems.
-- Gianluca Sartori
July 5, 2010 at 10:41 am
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
July 5, 2010 at 2:23 pm
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?
July 5, 2010 at 2:54 pm
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
July 5, 2010 at 3:02 pm
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.
July 5, 2010 at 3:10 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply