x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 78

# SQl help with selection

I have a lookup table called Bill which haves actives and inactive values

KIndly help with the logic in stored proc so i get proper values if user selects various combinations of valid and invalid Bills in selection
IN Code I am showing two different dropdown list one for actives and one for inactives where both have select all and select none values

Now I pass a null value if user wants to select all the bill values and pass -1 if user does not want to get any values.

Now if user selects all for actives and selects none for in actives my stored proc is failing please help:

Values in Bill Table
ID     Value    Isvalid
-1                   True
2      Test1      True
3     Test2      False

Values in Table A
ID  BillID   Name
1     2         John
2     3         Jim

select * from Table A
left outer Join Bill on Bill.ID = A.BillID

where @ActiveBillID is NULL or A.BillID = @ActiveBillID and
@InActiveBillID is NULL or A.BillID = @InActiveBillID
0
welcome 123
• 5
• 3
• 3
• +1
1 Solution

Commented:
EDIT: First thing that stands out is you must use parenthesis when mixing AND / OR to ensure the operations occur in the desired order.  Not sure I completely understand the logic, but assuming you want to return records where both conditions are true:

WHERE  ( @ActiveBillID is NULL OR A.BillID = @ActiveBillID )
AND
( @InActiveBillID is NULL OR  A.BillID = @InActiveBillID )
1

Author Commented:
Yes I do have Parenthesis already  by mistake didn't post it above.

I need the logic where I pass various combinations like NUll, _1 or _1 NUll, 2,3 etc values of Bill ID for active and inactive parameters.

If user select Null for Active(which means select all) and -1 for inactive which means select nothing I am not getting values and vice versa
0

Commented:
If user select Null for Active(which means select all) and -1 for inactive which means select nothing I am not getting values and vice versa

Probably because you're matching on the same ID, which can only have a single value. So if the @ActiveBillID  and @InActiveBillID values are different, this would never be true

WHERE  ( @ActiveBillID is NULL OR A.BillID = @ActiveBillID )
AND      ( @InActiveBillID is NULL OR  A.BillID = @InActiveBillID )

You could try using OR  instead:

WHERE   @ActiveBillID is NULL
OR           A.BillID = @ActiveBillID
OR          @InActiveBillID is NULL
OR          A.BillID = @InActiveBillID

... but not sure I understand the filtering/relationship between the two tables.  Using the sample data, what results are you expecting?
0

Database ExpertCommented:
try this..

Table creation

``````--

CREATE TABLE Bill
(
ID INT
,Value VARCHAR(25)
,Isvalid VARCHAR(5)
)
GO

INSERT INTO Bill VALUES
(-1  ,   NULL         ,  'True' ),
(2   ,   'Test1'    ,  'True'),
(3   ,   'Test2'     , 'False')
GO

CREATE TABLE TableA
(
ID INT
,Value VARCHAR(25)
,Name VARCHAR(50)
)
GO

INSERT INTO TableA VALUES
(1  ,   2         ,'John'),
(2  ,   3         ,'Jim')
GO
``````

Query to fetch data

NULL trail

``````--

DECLARE @ActiveBillID AS INT = NULL

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END

--
``````

Output
------------------------
ID      Value      Isvalid      ID      Value      Name
-1      NULL      True      NULL      NULL      NULL
2      Test1      True      2      3      Jim
3      Test2      False      NULL      NULL      NULL

``````--

DECLARE @ActiveBillID AS INT = -1

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END

--
``````

Output
------------------------
ID      Value      Isvalid      ID      Value      Name

GO

``````--

DECLARE @ActiveBillID AS INT = 2

SELECT * FROM Bill b
LEFT OUTER JOIN TableA a ON b.ID = a.ID
WHERE b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END

--
``````

Output
------------------------
ID      Value      Isvalid      ID      Value      Name
2      Test1      True      2      3      Jim

Hope it helps !!
0

Author Commented:
Thanks for the help all experts. I am sorry I did not explain the question clearly as was in a hurry my bad.

Expert Pawan: The above query with examples you gave is fantastic. In your first example if I pass @ActiveBillID as Null then  ID 3 will not return as its Isvalid column is False which turns out to be inactive

@ActiveBillID  = all Bill IDS where Isvalid is True
@InActiveBillID = all Bill ID's where isValid is False

So now I need to pass both @ActiveBillID (which are the Id values of Isvalid true Ids in bill table ) and also @InActiveBillID parameters together which could be cominations like:
@ActiveBillID  = Null,  @InActiveBillID = Null
Result: all the bill id values will be returned as user selected Select ALL for both
ID  BillID   Name
1     2         John
2     3         Jim

@ActiveBillID  = -1,  @InActiveBillID = -1

@ActiveBillID  = Null,  @InActiveBillID = -1
Result:  User selected Select ALL for ActiveBILL and SelectNone for InactiveBill
ID  BillID   Name
1     2         John

@ActiveBillID  = -1,  @InActiveBillID = Null
Result: User selected None for ActiveBill and Select All for InactiveBill
ID  BillID   Name
2     3         Jim

@ActiveBillID  =2 ,  @InActiveBillID = 3
Result: User selected a value for each of ActiveBill as well as Inactive Bill from their respective dropdownlists
ID  BillID   Name
1     2         John
2     3         Jim

@ActiveBillID  =2 ,  @InActiveBillID = -1
Result: User selected a value for each of ActiveBill and select None for Inactive Bill
ID  BillID   Name
1     2         John

and you can imagine some more like the above
0

Author Commented:
I used the below where I know there are mistakes and hitting me huge syntax errors obviously

A.ID  in ( CASE WHEN @ActiveBillID  IS NULL and  @InActiveBillID IS NULL THEN A.ID
WHEN @ActiveBillID  = -1 and   @InActiveBillID = -1 THEN -999999
WHEN @ActiveBillID  IS NULL and  @InActiveBillID = -1 THEN @ActiveBillID
WHEN @ActiveBillID  = -1 and  @InActiveBillID IS NULL THEN  @InActiveBillID
WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID IS NOT NULL THEN @ActiveBillID , @InActiveBillID

WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID IS NULL THEN @ActiveBillID
WHEN @ActiveBillID  IS NULL AND  @InActiveBillID IS NOT NULL THEN  @InActiveBillID
WHEN @ActiveBillID  IS NOT NULL AND  @InActiveBillID = -1 THEN @ActiveBillID
WHEN @ActiveBillID  = -1 AND  @InActiveBillID IS NOT NULL THEN  @InActiveBillID
)
0

Database ExpertCommented:
Here it is complete code --  <<Used tables I created above.>>

--1. Try1

DECLARE @ActiveBillID AS INT = NULL
DECLARE @InActiveBillID AS INT = NULL

``````--

DECLARE @ActiveBillID AS INT = NULL
DECLARE @InActiveBillID AS INT = NULL

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID
WHEN @InActiveBillID = -1 THEN -999999
ELSE
@InActiveBillID
END AND b.Isvalid = 'False' ) )

GO

--
``````

Output
---------------------

ID      Value      Name
1      2          John
2      3          Jim

--1. Try2
DECLARE @ActiveBillID AS INT = -1
DECLARE @InActiveBillID AS INT = -1

``````--

DECLARE @ActiveBillID AS INT = -1
DECLARE @InActiveBillID AS INT = -1

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID
WHEN @InActiveBillID = -1 THEN -999999
ELSE
@InActiveBillID
END AND b.Isvalid = 'False' ) )

GO

--
``````

Output
---------------------

ID      Value      Name

--1. Try3
DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = 3

``````--

DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = 3

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID
WHEN @InActiveBillID = -1 THEN -999999
ELSE
@InActiveBillID
END AND b.Isvalid = 'False' ) )

GO

--
``````

Output
---------------------
ID      Value      Name
1      2         John
2      3         Jim

--1. Try4
DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = -1

``````--

DECLARE @ActiveBillID AS INT = 2
DECLARE @InActiveBillID AS INT = -1

SELECT a.* FROM Bill b
FULL OUTER JOIN TableA a ON CAST(a.Value AS INT) = b.ID
WHERE a.ID IS NOT NULL
AND ( ( b.ID  = CASE WHEN @ActiveBillID IS NULL THEN b.ID
WHEN @ActiveBillID = -1 THEN -999999
ELSE
@ActiveBillID
END AND b.Isvalid = 'True' )
OR   ( b.ID  = CASE WHEN @InActiveBillID IS NULL THEN b.ID
WHEN @InActiveBillID = -1 THEN -999999
ELSE
@InActiveBillID
END AND b.Isvalid = 'False' ) )

GO

--
``````

Output
---------------------

ID      Value      Name
1      2              John

Hope it helps!!
0

MSSQL Senior EngineerCommented:
I can only see this done by using a dynamic SQL or have some queries built and run the one for the case you want:
``````IF @ActiveBillID IS NULL AND @InActiveBillID IS NULL
select *
from Table A
Left outer Join Bill on Bill.ID = A.BillID
ELSE IF @ActiveBillID IS NULL
select *
from Table A
Left outer Join Bill on Bill.ID = A.BillID
where A.BillID = @InActiveBillID
ELSE IF @InActiveBillID IS NULL
select *
from Table A
Left outer Join Bill on Bill.ID = A.BillID
where A.BillID = @ActiveBillID
ELSE
select *
from Table A
Left outer Join Bill on Bill.ID = A.BillID
where A.BillID = @ActiveBillID OR A.BillID = @InActiveBillID
``````
0

MSSQL Senior EngineerCommented:
And this is the version for dynamic SQL:
``````DECLARE @MySQL VARCHAR(MAX)
DECLARE @MyFilter VARCHAR(MAX) = ''

SET @MySQL = 'select *
from Table A
Left outer Join Bill on Bill.ID = A.BillID'

IF @ActiveBillID IS NOT NULL
SET @MyFilter = @MyFilter + ' WHERE A.BillID = ' + CAST(@ActiveBillID AS VARCHAR)
ELSE IF @InActiveBillID IS NOT NULL
IF @MyFilter = ''
SET @MyFilter = @MyFilter + ' WHERE A.BillID = ' + CAST(@InActiveBillID AS VARCHAR)
ELSE
SET @MyFilter = @MyFilter + ' OR A.BillID = ' + CAST(@InActiveBillID AS VARCHAR)

SET @MySQL = @MySQL + @MyFilter

EXEC(@MySQL)
``````
0

Database ExpertCommented:
@Author - Have you tried the code I gave?

Thank you!
0

Author Commented:
Thanks a Lot Expert Pawan . It worked like a charm. I am really impressed with your test cases which made the requirement even clear to myself. You are simply awesome. I am so glad I pay experts exchange just because of experts like you.

I am sorry I get a chance to try your solution expert  Vitor Montalvão and sure that might work too but caught with a already working solution by Expert Pawan.
0

Author Commented:
Thank You!
0

MSSQL Senior EngineerCommented:
I am sorry I get a chance to try your solution expert  Vitor Montalvão and sure that might work too but caught with a already working solution by Expert Pawan.
You're the client you should choose what's best for you. We are only giving you options but the last call is always yours.
I just hope that you understand the differences from one solution to the others. When you have time just run all the solutions to check if they are working. If they do you then you can also try to check the performance differences so you can understand them better for future issues.
Cheers.
1

Commented:
Thanks for the help all experts. I am sorry I did not explain the question clearly as was in a hurry my bad.

Ohh.. those examples clarify things, thanks. Personally, I would have gone with a UNION. Though a bit more verbose, it makes the intent of the query it a bit more obvious than with CASE statements IMO.  Edit: Though as Victor mentioned, it is always a good idea to understand the different options and ramifications of each one.

``````--- Get specified "active" records
SELECT a.ID, a.Name
FROM   TableA a INNER JOIN Bill b ON a.BillID = b.ID
WHERE  a.BillID = b.ID
AND    b.IsValid = 'True'
AND   ( @ActiveBillID IS NULL OR a.BillID = @ActiveBillID )
UNION ALL
--- Get specified "inactive" records
SELECT a.ID, a.Name
FROM   TableA a INNER JOIN Bill b ON a.BillID = b.ID
WHERE  a.BillID = b.ID
AND    b.IsValid = 'False'
AND   ( @InActiveBillID IS NULL OR a.BillID = @InActiveBillID )
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.