[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

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
Asked:
welcome 123
  • 5
  • 3
  • 3
  • +1
1 Solution
 
_agx_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
 
welcome 123Author 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
 
_agx_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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Pawan KumarDatabase 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

Open in new window



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


--

Open in new window


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


--

Open in new window



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


--

Open in new window



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

Hope it helps !!
0
 
welcome 123Author 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
Result: NO results as user selected Select None for both

@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
 
welcome 123Author 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
 
Pawan KumarDatabase 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

--

Open in new window






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

--

Open in new window





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

--

Open in new window





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

--

Open in new window


 


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


ID      Value      Name
1      2              John




Hope it helps!!
0
 
Vitor MontalvãoMSSQL 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 

Open in new window

0
 
Vitor MontalvãoMSSQL 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)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Author - Have you tried the code I gave?

Thank you!
0
 
welcome 123Author 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
 
welcome 123Author Commented:
Thank You!
0
 
Vitor MontalvãoMSSQL 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
 
_agx_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 )

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now