Menu
I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it.
I was doing it like this:
(@thing IS NULL or Thing=@thing) Is this right, and if so, would it perform badly? It's seems to be a lot slower than constructing the SQL separately.
What's the optimal way to do this?
FIXED! See Marc Gravell's answer. In summary using IS NULL many times is a big performance hit.
748
COUNT(1) and COUNT(*) are completely interchangeable and both yield same results – both do NOT ignore NULL values and both return the number of rows of a particular table. It counts all the duplicate values (number 5 repeats two times) and based on results, SQL server counts both observations.
Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.
If you want a parameter that can filter by a certain value, but when you have no value in your parameter, SQL returns no results. When the parameter has no value, SQL interprets it as null in your code. Null means no value. You can fix this problem by adding a code to fix the null case.
Once you get more than a couple of these, then yes: it starts to get pretty slow. In such cases, I tend to use generated TSQL - i.e.
DECLARE @sql nvarchar(4000) SET @sql = /* core query */ IF @name IS NOT NULL SET @sql = @sql + ' AND foo.Name = @name' IF @dob IS NOT NULL SET @sql = @sql + ' AND foo.DOB = @dob' // etc EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime', @name, @dob etc
Note that sp_ExecuteSQL caches query-plans, so any queries with the same args can potentially re-use the plan.
The downside is that unless you sign the SPROC, the caller needs SELECT permissions on the table (not just EXEC permissions on the SPROC).
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
