Help with this Stored Procedure

[Jeff via AccessMonster.com]

I have a stored procedure that I use to return a filtered list but I’m having
a problem with null values returned with joined tables. Here’s my code:

@Tbl1Var1 As nvarchar(50), @ Tbl1Var2 As nvarchar(50), @ Tbl1Var3 As nvarchar
(50), @Tbl1Var4 As nvarchar(50), @ Tbl1Var5 As nvarchar(50), @ Tbl2Var1 As
nvarchar(50)
AS
SELECT * FROM Tbl1 WHERE ID in
(SELECT ID FROM Tbl1
LEFT OUTER JOIN Tbl2 ON Tbl1.ID=Tbl2.ID
WHERE
Tbl1Var1 LIKE @Tbl1Var1 AND
Tbl1Var2 LIKE @Tbl1Var2 AND
Tbl1Var3 LIKE @Tbl1Var3 AND
Tbl1Var4 LIKE @Tbl1Var4 AND
Tbl1Var5 LIKE @Tbl1Var5 AND
Tbl2Var1 LIKE @Tbl2Var1)

Tbl1 and Tbl2 have a one to many relationship but if I set Tbl2Var = %
(wildcard) I only get back those records for which Tbl2Var1 Is Not Null.
Removing the last line (Tbl2Var1 LIKE @Tbl2Var) with all the other variables
= % returns the correct number of records.

I’m guessing I need an if statement to check for Tbl2var1 = null but my
attempts are proving unsuccessful. Any help is appreciated.

 

[Sylvain Lafontaine]

You need something like:

.... (Tble2Var1 is Null OR Tbl2Var1 LIKE @Tbl2Var1) ...

 

[Jeff via AccessMonster.com]

the only problem with that is that if specify a specific value for @tbl2Var1
I will also get null records which I don't want.

You need something like:

... (Tble2Var1 is Null OR Tbl2Var1 LIKE @Tbl2Var1) ...

I have a stored procedure that I use to return a filtered list but I'm
having

[quoted text clipped - 24 lines]

I'm guessing I need an if statement to check for Tbl2var1 = null but my
attempts are proving unsuccessful. Any help is appreciated.

 

[Sylvain Lafontaine]

First, you should be able to fine tune this without any problem by adding
all the required logical comparaisons that will suit your exact needs.

Second, I don't see the logic of using a Left Outer Join if you are not
interested with Null values on the right side.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

the only problem with that is that if specify a specific value for
@tbl2Var1
I will also get null records which I don't want.

You need something like:

... (Tble2Var1 is Null OR Tbl2Var1 LIKE @Tbl2Var1) ...

I have a stored procedure that I use to return a filtered list but I'm
having

[quoted text clipped - 24 lines]

I'm guessing I need an if statement to check for Tbl2var1 = null but my
attempts are proving unsuccessful. Any help is appreciated.

 

[Jeff via AccessMonster.com]

I am interested in the null values in certain cases (i.e. wildcard should
return all records from left side). I thought the solution would be a lot
easier but I guess I'll have to re-think this one.

First, you should be able to fine tune this without any problem by adding
all the required logical comparaisons that will suit your exact needs.

Second, I don't see the logic of using a Left Outer Join if you are not
interested with Null values on the right side.

the only problem with that is that if specify a specific value for
@tbl2Var1

[quoted text clipped - 9 lines]

 

[chakatheapeman]

Union Select

Sometimes I use a Union Select to combine the results of two queries. One select with null and another with aggregate amounts, for example situations like a summation of received items and items that have not been received yet.