Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 7:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:07 AM
Points: 828, Visits: 1,702
Hey all,

Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables:

CREATE TABLE TableA
(
ID INT IDENTITY PRIMARY KEY,
Val VARCHAR(10)
)

CREATE TABLE LinkTable
(
ID INT IDENTITY PRIMARY KEY,
TableA_ID INT,
TableB_ID INT
)

CREATE TABLE TableB
(
ID INT IDENTITY PRIMARY KEY,
Val VARCHAR(10)
)

Now, suppose I want to have a stored procedure, in which I get back all of the records from TableA. The stored procedure should have an input parameter, which defaults to NULL, which will allow me to search for values from TableA which have a corresponding value linked to TableB.

Normally, I would write the query in the form:

SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ON TableA_ID = A.ID
AND TableB_ID = ISNULL(@TableB_ID, TableB_ID)

The problem of course, is that this won't actually filter the result set, because I'm using a LEFT JOIN. Of course, if I were to change it to be a JOIN, then I would be forcing it to return only records from TableA which have a corresponding TableB value, which I also don't want.

Normally, I'd solve this by using a format similar to the following:

SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ON TableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE TableB_ID END) = TableB_ID

The problem here is that if there is no record in LinkTable for TableB, then the WHERE clause would evaluate to (Value = NULL), which will always return false. Normally, I'd handle that by saying (Value IS NULL), but there is no way for me to modify my CASE statement to produce that kind of output.

The only solution I've been able to come up with is the following:

DECLARE @TableB_ID INT

IF @TableB_ID IS NULL
SELECT
ID
FROM TableA
ELSE
SELECT
A.ID
FROM TableA A
JOIN LinkTable ON TableA_ID = A.ID
AND TableB_ID = @TableB_ID

But this solution is kinda ugly, since it forces me to use an IF-ELSE statement in a stored proc, or to just use two stored procs, neither of which I like.

Does anyone have a better solution?
Post #1386129
Posted Monday, November 19, 2012 6:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:07 AM
Points: 828, Visits: 1,702
Hm. On thinking about it a bit more, I guess I could use a subquery:

SELECT
A.ID
FROM TableA A
WHERE
(
CASE
WHEN @TableB_ID IS NOT NULL THEN
CASE
WHEN EXISTS (SELECT 1 FROM LinkTable WHERE TableA_ID = A.ID AND TableB_ID = @TableB_ID) THEN 1
ELSE 0
END
ELSE 1
END
) = 1

Also very ugly though...

And one other solution I've come up with:

SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ON TableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE ISNULL(TableB_ID, -1) END) = ISNULL(TableB_ID, -1)

This one of course is reliant upon using a number for the ISNULL function which is guaranteed to never occur. Sure, it might work in some cases, but it still feels more like a hack than anything else.
Post #1386336
Posted Monday, November 19, 2012 10:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
Before using one or another technique, you should read this article from one of the best experts in this area:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386491
Posted Monday, November 19, 2012 10:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:07 AM
Points: 828, Visits: 1,702
Yeah - I've used the Dynamic SQL approach before, and it does work pretty well, but I'm not a huge fan of it. Never knew about the RECOMPILE option though, I'll have to try that out.

Still, doesn't really help with the question at hand. I mean, yes - I could use the dynamic SQL solution, and that would solve my problem. If there is no better, more elegant solution, then I suppose I'll just go with that.
Post #1386506
Posted Monday, November 19, 2012 10:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
kramaswamy (11/19/2012)
Yeah - I've used the Dynamic SQL approach before, and it does work pretty well, but I'm not a huge fan of it. Never knew about the RECOMPILE option though, I'll have to try that out.

Still, doesn't really help with the question at hand. I mean, yes - I could use the dynamic SQL solution, and that would solve my problem. If there is no better, more elegant solution, then I suppose I'll just go with that.


As per article, Dynamic SQL, most likely, would give you the best performance, but if you insist, there is another way to write your query:

        DECLARE @TableB_ID INT

SELECT DISTINCT
A.ID
FROM TableA A
LEFT JOIN LinkTable LT ON LT.TableA_ID = A.ID
WHERE @TableB_ID IS NULL
OR LT.TableB_ID = @TableB_ID



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386510
Posted Monday, November 19, 2012 11:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:07 AM
Points: 828, Visits: 1,702
Hm - I'm actually kinda surprised that works. I would have thought it would fail because the WHERE condition would evaluate AS

@TableB_ID IS NULL (TRUE) OR TableB_ID (Value OR NULL) = @TableB_ID (NULL)

I would have thought that the second part of that would evaluate to undefined, since you are comparing a NULL value with an equals operator. Then, since that one is undefined, the whole condition would evaluate to undefined, since you're ORing TRUE with undefined.

I guess that the second condition though, will instead evaluate as FALSE instead of undefined, and thus allow it to work?
Post #1386511
Posted Monday, November 19, 2012 11:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
kramaswamy (11/19/2012)
Hm - I'm actually kinda surprised that works. I would have thought it would fail because the WHERE condition would evaluate AS

@TableB_ID IS NULL (TRUE) OR TableB_ID (Value OR NULL) = @TableB_ID (NULL)

I would have thought that the second part of that would evaluate to undefined, since you are comparing a NULL value with an equals operator. Then, since that one is undefined, the whole condition would evaluate to undefined, since you're ORing TRUE with undefined.

I guess that the second condition though, will instead evaluate as FALSE instead of undefined, and thus allow it to work?


No, that is not exactly right. The second condition is not evaluated as FALSE and it's easy to check, let see the following example:

          select *
from (values (1),(2),(3)) c(c)
where not (c = null)

if "c=null" evaluates as FALSE, then "not (c = null)" would evaluate to true and all records will be returned. But it doesn't happen as "c = null" evaluates to UNDEFINED, as you rightly thought in the first place!
The important bit in the used where condition is "OR", since OR evaluates to TRUE if any of the logical parts evaluates to TRUE, it doesn't really care that "TableB_ID = @TableB_ID(NULL)" evaluates as UNDEFINED!
So, used WHERE works simply like that:

If @TableB_ID is not null, it will check if the value in TableB_ID from LinkedTable is equal to it, basically turning LEFT JOIN to INNER JOIN!
If @TableB_ID is null, JOIN is not working at all as condition evaluates to UNDEFINED, but this is ignored completely as part of evaluating result of logical OR operator, which returns true for @TableB_ID IS NULL


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386515
Posted Tuesday, November 20, 2012 6:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I think this may do what you are trying to do. If the search term is in both tables then the matching row only from Table A displays, otherwise ALL the rows in Table A are displayed.


IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA

IF OBJECT_ID('tempdb..#LinkTable') IS NOT NULL
DROP TABLE #LinkTable

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
DROP TABLE #TableB

CREATE TABLE #TableA (
[ID] INT NOT NULL,
[Val] VARCHAR(10) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

CREATE TABLE #LinkTable(
[ID] INT IDENTITY(1,1) NOT NULL,
[TableA_ID] INT NULL,
[TableB_ID] INT NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

CREATE TABLE #TableB (
[ID] INT NOT NULL,
[Val] VARCHAR(10) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

INSERT INTO #TableA
SELECT 1,'Apples'
UNION
SELECT 2,'Pears'
UNION
SELECT 3,'Oranges'
UNION
SELECT 4,'Grapes'

INSERT INTO #TableB
SELECT 1,'Oranges'
UNION
SELECT 2,'Onions'
UNION
SELECT 3,'Apples'
UNION
SELECT 4,'Squash'

INSERT INTO #LinkTable
SELECT 1,3
UNION
SELECT 3,1


DECLARE @strSearch VARCHAR(50)
SET @strSearch = 'Apples'
--SET @strSearch = 'Pears'
--SET @strSearch = 'Oranges'
--SET @strSearch = 'Onions'
--SET @strSearch = ''

SELECT
a.ID
,a.Val
FROM
#TableA AS a
LEFT OUTER JOIN
#LinkTable AS lt
ON a.ID = lt.TableA_ID
LEFT OUTER JOIN
#TableB AS b
ON lt.TableB_ID = b.ID
WHERE

--if @strSearch in A and in B then matching row from A
((a.Val = ISNULL(NULLIF(@strSearch,''),0)
AND (b.Val = ISNULL(NULLIF(@strSearch,''),0)))

--if @strSearch not A or not B then ALL rows from A
OR
((NOT EXISTS (SELECT ID FROM #TableA WHERE val = @strSearch)
OR NOT EXISTS (SELECT ID FROM #TableB WHERE val = @strSearch))
AND EXISTS (SELECT ID FROM #TableA)))


Post #1387201
Posted Wednesday, November 21, 2012 5:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
Steven Willis (11/20/2012)
I think this may do what you are trying to do. If the search term is in both tables then the matching row only from Table A displays, otherwise ALL the rows in Table A are displayed.


IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA

IF OBJECT_ID('tempdb..#LinkTable') IS NOT NULL
DROP TABLE #LinkTable

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
DROP TABLE #TableB

CREATE TABLE #TableA (
[ID] INT NOT NULL,
[Val] VARCHAR(10) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

CREATE TABLE #LinkTable(
[ID] INT IDENTITY(1,1) NOT NULL,
[TableA_ID] INT NULL,
[TableB_ID] INT NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

CREATE TABLE #TableB (
[ID] INT NOT NULL,
[Val] VARCHAR(10) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

INSERT INTO #TableA
SELECT 1,'Apples'
UNION
SELECT 2,'Pears'
UNION
SELECT 3,'Oranges'
UNION
SELECT 4,'Grapes'

INSERT INTO #TableB
SELECT 1,'Oranges'
UNION
SELECT 2,'Onions'
UNION
SELECT 3,'Apples'
UNION
SELECT 4,'Squash'

INSERT INTO #LinkTable
SELECT 1,3
UNION
SELECT 3,1


DECLARE @strSearch VARCHAR(50)
SET @strSearch = 'Apples'
--SET @strSearch = 'Pears'
--SET @strSearch = 'Oranges'
--SET @strSearch = 'Onions'
--SET @strSearch = ''

SELECT
a.ID
,a.Val
FROM
#TableA AS a
LEFT OUTER JOIN
#LinkTable AS lt
ON a.ID = lt.TableA_ID
LEFT OUTER JOIN
#TableB AS b
ON lt.TableB_ID = b.ID
WHERE

--if @strSearch in A and in B then matching row from A
((a.Val = ISNULL(NULLIF(@strSearch,''),0)
AND (b.Val = ISNULL(NULLIF(@strSearch,''),0)))

--if @strSearch not A or not B then ALL rows from A
OR
((NOT EXISTS (SELECT ID FROM #TableA WHERE val = @strSearch)
OR NOT EXISTS (SELECT ID FROM #TableB WHERE val = @strSearch))
AND EXISTS (SELECT ID FROM #TableA)))




Why would you go with such over-kill? OP doesn't need LinkTable, His TableB has FK directly to TableA.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1387383
Posted Friday, November 23, 2012 6:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Eugene Elutin (11/21/2012)Why would you go with such over-kill? OP doesn't need LinkTable, His TableB has FK directly to TableA.

First of all, in the OP the example is using a "link" table. So in my example I used one too. If you will notice in my sample data, the keys in the first table neither match those in the second table nor are all of the keys even in the "link" table. Since a "link" table WAS described in the OP I assumed that such relationships (or lack thereof) would have to be related by the cross-reference or there wouldn't be a need for the "link" table (as you pointed out).

Whether or not this is a good schema design is beside the point. If Table1 and Table2 did have a FK relationship then sure the middle join could be left out and the where clause simplified.

If what I posted is useful to anyone (especially Kramaswamy) then I met my objective. If I've misunderstood or misinterpreted his requirements based on insufficient data (or maybe even because I'm just tired and cranky at times) and the code I posted has no value in this specific case...well, then someone can provide a better solution and I will be pleased to add it to my T-SQL toolbox.

 
Post #1388263
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse