SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs


Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs

Author
Message
kramaswamy
kramaswamy
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5276 Visits: 1848
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?
kramaswamy
kramaswamy
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5276 Visits: 1848
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.
Eugene Elutin
Eugene Elutin
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16390 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
kramaswamy
kramaswamy
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5276 Visits: 1848
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.
Eugene Elutin
Eugene Elutin
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16390 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
kramaswamy
kramaswamy
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5276 Visits: 1848
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?
Eugene Elutin
Eugene Elutin
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16390 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2701 Visits: 1721
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)))



Eugene Elutin
Eugene Elutin
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16390 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2701 Visits: 1721
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.

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search