Ignore Null parameters in a single query

[theguyinthehat]

I'm programming a form where people select information to limit information they want to see. There are 10 fields, and they can choose between 0 and 10. When they click the command button, the code lifts the values out of each of the 10 comboboxes and filters it in a sql query. Is there a way to get the Select statement to disregard operations on columns assigned null parameters? Could I assign * to the null fields and have it read out like an asterisk in SQL?

[rjv_rnjn]

You can form your SQL like

select col1, col2
from table1
where col1 = ISNULL(@col1ValueToCompare, col1)

and similar for all the columns.

[theguyinthehat]

I figured it out with temp tables...but it won't let me do this (@ln is parameter)

[code/]
If @ln is not null
SELECT * Into #Temp1 FROM [#Lots] WHERE [Lot Number] = @ln
Else
Select * Into #Temp1 From #Lots
Drop table #Lots
[/code]

even though the conditionals are mutually exclusive. I have a chain of about 10, as I said, or else I'll end up making 2^10 temps....the replace feature won't work, it won't accept an asterisk and I don't want to filter results if the parameter is null

[rjv_rnjn]

Temp table is not required here, I believe. Did you try implementing ISNULL?

[si_the_geek]

You seem to be having problems with the code tags.. either use the button for it, or type the tags properly: [code] code here [/code]


I think your issue can be solved by using the method shown in the article Stored procedures: How can I add optional parameters to a Where clause, without building the statement in a string? from our Database Development FAQs/Tutorials (at the top of this forum)

It is a little different to rjv_rnjn's suggestion, but basically the same idea.

[rjv_rnjn]

@si_the_geek: Is there a reason you suggest checking NULL's that way (performance wise or some known issues)?

I think using ISNULL() is quite clean when you have to do that for multiple parameters.

[theguyinthehat]

thank you for the code tags...I've asked every responder the proper tags and you're the first to tell me.

If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table? If the parameter is null, then I don't want to run a query, I want to move to the next parameter. If the parameter is not null, I want to filter the corresponding column. And there are 10 different parameters that I may or may not filter in this way.

[rjv_rnjn]

If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table?

I think I'm missing your point.
Do you mean each column that you are looking at belongs to a separate table? In that case your query could look like

 select t1.Col1, t2.Col2
from table1 t1
inner join table2 t2 on t1.id = t2.id
where t1.col1 = ISNULL(@col1ValueToCompare, col1) AND t2.col2 = ISNULL(@col2ValueToCompare, t2.col2)

& so on.

If you are worried about the extra reads that the SQL server has to perform for the fetch to occur (in case of ISNULL), then you shouldn't -- it's equivalent to if it's not there. Even if there's a performance hit, it's minimal (I've never tried to get any stats on that).

BUT, if you mean that if column1 is selected as filter then you look for values from Table1 then you can use the if..else. It still doesn't make clear why would you use temp tables.

A more detailed explanation of your problem could help us give you a suggestion.

[si_the_geek]

@si_the_geek: Is there a reason you suggest checking NULL's that way (performance wise or some known issues)?

I think using ISNULL() is quite clean when you have to do that for multiple parameters.

The method in the FAQ article is generic SQL so can be used for most database systems, whereas IsNull is specific to a particular DBMS (most have something similar, but you need to know the name and syntax each time).

IsNull is shorter to write, but the method in the FAQ is likely to run more quickly - as I will explain below.

If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table?

In theory yes - for each row it will compare the field to itself... but the database system might be clever enough to notice that there is no point, and just skip the work.

Using the method in the FAQ virtually guarantees that the work will be skipped, which is explained by this piece of text in the article:

The first condition will only be checked once for the entire query (just as the If statement was), because the value is fixed at the time the query starts - it does not change for each row.

When the first condition is True, the second will not be checked at all - because the result of an Or will be True if either or both parts are True (ie: because the first condition is True, it doesn't matter whether or not the second condition is)

[rjv_rnjn]

Good to know that.
Interestingly, it seems I never reached the bottom of the MSDN article or conveniently forgot it

C. Testing for NULL in a WHERE clause
Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.

[si_the_geek]

I presume that comment is actually referring to a different situation, but it is hard to be sure without seeing the rest of the article.

I haven't tested, but for this situation I wouldn't be surprised if your method is actually equal to the FAQ method (which is equal to [or better than] using an If statement to build the query).

[theguyinthehat]

Ok--I need separate tables because I need to run subsequent queries on tables that have already been filtered. This would do the job, if it would compile (perhaps not very efficiently, but it would do it):

ALTER PROCEDURE [RSTA\TAHuth].[spRelotter]
@ln VARCHAR(4),
@ton VARCHAR(20),
@cn VARCHAR(10),
@En VARCHAR(30),
@Wn VARCHAR(5),
@ten VARCHAR(30),
@pn VARCHAR(20),
@dn VARCHAR(5) 

AS
SELECT * INTO #Lots From [FPAD Lot Data]

If @ln is not null
SELECT * Into #Temp1 FROM [#Lots] WHERE [Lot Number] = @ln
Else
Select * Into #Temp1 From #Lots
Drop table #Lots

If @ton is not null
SELECT * into #Temp2 FROM #Temp1 WHERE [Tool] = @ton
Else
SELECT * into #Temp2 FROM #Temp1
Drop Table #Temp1

If @cn is not null
Select * into #Temp3 From #Temp2 WHERE [Charge Number] = @cn
Else
Select * into #Temp3 From #Temp2
Drop #Temp2


--etcetera, but it won't compile!!!
--Finally, output to vb.net
SELECT * INTO [#Lots] FROM [#Temp9]

	/* SET NOCOUNT ON */ 
	RETURN

I don't understand how you want me to use Is null or isnotNull()... hopefully you can understand what i'm trying to do, I wasn't very clear before. I have to do the 'else' query or I'd have so many different queries to run to cover every permutation of non-null values.

[Pradeep1210]

Instead of using temp tables in this manner, I think what rjv_rnjn suggested would be much more efficient with same output result.

[theguyinthehat]

I think that would return many different instances of queries on the same table rather than compounding all the relevant (ie. non-null) queries on the same table. I could take the intersection of all the different queries and get the same result...let me see if there's a way to do that? I don't see how a chain of isnull() would compound the queries...but I could be missing it completely

[rjv_rnjn]

Try this:

ALTER PROCEDURE [RSTA\TAHuth].[spRelotter]
@ln VARCHAR(4),
@ton VARCHAR(20),
@cn VARCHAR(10),
@En VARCHAR(30),
@Wn VARCHAR(5),
@ten VARCHAR(30),
@pn VARCHAR(20),
@dn VARCHAR(5) 

AS

SELECT * FROM [FPAD Lot Data]
WHERE ((@ln IS NULL) OR (@ln = [Lot Number]))
AND ((@ton IS NULL) OR (@ton = [Tool]))
AND ((@cn IS NULL) OR (@cn = [Charge Number]))
/*And similarly for other variables like @En, @Wn etc..*/
END

[rjv_rnjn]

I re-read your post and this line struck me

Ok--I need separate tables because I need to run subsequent queries on tables that have already been filtered.

It makes me think you do not view SQL queries as set based but procedural in nature. I would suggest going through this article that explains this thing in much more detail (and in a better way than I can).

[theguyinthehat]

of course... Yes that makes sense. Really wish I'd thought of that. That's embarrassing. Thanks a lot!

And I'll read it. I'm a Materials engineer, and now having to organize all this programming...getting used to it.

[rjv_rnjn]

I presume that comment is actually referring to a different situation, but it is hard to be sure without seeing the rest of the article.

I haven't tested, but for this situation I wouldn't be surprised if your method is actually equal to the FAQ method (which is equal to [or better than] using an If statement to build the query).

The comment is for checking null values in where condition:

SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;

Performance wise I had tested initially using ISNULL() over "IS NULL" and didn't find any time deterioration for some huge data set that I had (I was looking only in seconds). So it may be equivalent in performance; but I was "sold" by the logic that you had for using it.
{I even used it already for the sample code given to OP}.

[rjv_rnjn]

of course... Yes that makes sense. Really wish I'd thought of that. That's embarrassing. Thanks a lot!

And I'll read it. I'm a Materials engineer, and now having to organize all this programming...getting used to it.

Glad to be of help!

Please mark the thread as resolved if there are no follow-up questions.