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.
I'm not sure if it is the 'optimal' way, but this is exactly what I do in my stored procedures for the same purposes. My gut feeling is that this is faster than a dynamically created query purely from an execution plan standpoint. The other option is to create a query for each combination of these "flags" that you are passing in, but that really isn't that scalable.
I would handle it this way.
WHERE Thing = ISNULL(@Thing, Thing)
If you're just using the parameter as a filter on the where clause, this will work very well. It will ignore the parameter if it is null.
This is the method I typically use. I see no reason for it to be inefficient, as the statement should short-circuit to true if @thing is null, and would therefore not require a table scan. Do you have any evidence that this comparison is slowing your query? If not, I would not worry about it.
when you declare the parameters if you set a value to them such as null in your case you do not need to pass a value in to them unless of course you need to. I use this ability to flag if another query needs to be run is special cases when the parameter is not null
I typically just check it like this
IF field IS NULL
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).
A technique I’ve used in the past for this scenario is to utilize the COALESCE function as part of my WHERE clause. Books Online will provide more in depth info on the function, but here’s a snippet of how you can use it in the scenario you described:
create procedure usp_TEST_COALESCE
(
@parm1 varchar(32) = null,
@parm2 varchar(32) = null,
@parm3 int = null
)
AS
SELECT *
FROM [TableName]
WHERE Field1 = COALESCE(@parm1, Field1)
AND Field2 = COALESCE(@parm2, Field2)
AND Field3 = COALESCE(@parm3, Field3)
The COALESCE function will return the first non-null expression from its arguments. In the example above, if any of the parameters are null, the COALESCE function will use the value in the underlying field.
One important caveat to using this technique is that the underlying fields in the table (that make up your where clause) need to be non-nullable.
I generally use
WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)
etc.
Thanks, This was helpful. I have decided to use the sp_ExecuteSQL method due to the potential performance advantages mentioned. I have a slightly different take on it which you may find helpful.
DECLARE @sql nvarchar(4000)
DECLARE @where nvarchar(1000) =''
SET @sql = 'SELECT * FROM MyTable'
IF @Param1 IS NOT NULL
SET @where = @where + ' AND Field1 = @Param1'
IF @Param2 IS NOT NULL
SET @where = @where + ' AND Field2 = @Param2'
IF @Param3 IS NOT NULL
SET @where = @where + ' AND Field3 = @Param3'
-- Add WHERE if where clause exists, 1=1 is included because @where begins with AND
IF @where <> ''
SET @sql = @sql + ' WHERE 1=1' + @where
--Note that we could also create order parameters and append here
SET @sql = @sql + ' ORDER BY Field1'