Ignore Null parameters in a single query

[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.

[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.

[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.