December 27, 2010 at 12:38 am
Dear All,
I today i am stuck in a very strange problem. I have a table structure mention below
create table #M_Vendor
(
VendorID int, vendor varchar(12), contactperson varchar(12), email varchar(12), DailysettlementYN bit,phone1 varchar(12)
)
--Sample Data
INSERT INTO [#M_Vendor] ([VendorID],[vendor],[contactperson],,[DailysettlementYN],[phone1])VALUES(1,'Vijay','Vijay','email',1,'1234')
INSERT INTO [#M_Vendor] ([VendorID],[vendor],[contactperson],,[DailysettlementYN],[phone1])VALUES(2,'Sanjay','Sanjay','email1',0,'12345')
INSERT INTO [#M_Vendor] ([VendorID],[vendor],[contactperson],,[DailysettlementYN],[phone1])VALUES(3,'Unknown','Na','Na',NULL,'122345')
Sample data is given for your purpose.
Now I want to make filter on bit column if I pass 1 for DailysettlementYN column then all record with 1 should display.
If I Pass 0 for DailysettlementYN then all record with 0 value should display.
If I pass nothing means null then all record should display.
I tried it from my self but i have failed to put condition for null value, if i put condition for null value then condition on 0 value is gets failed
My Code
SELECT *
FROM #M_Vendor
WHERE
((
ISNULL(@imported, '') <> ''
AND
(
CASE
WHEN @imported = 0 AND DailysettlementYN = 0 THEN 1
WHEN @imported = 1 AND DailysettlementYN = 1 THEN 1
--WHEN ISNULL(@imported, '') = '' THEN 1
else 0
END
) = 1) OR ISNULL(@imported, '') = '' )
Kindly provide me some way to solve this problem
December 27, 2010 at 2:25 am
This is very, very ugly. Flags like these must have only two distinct values. With 1,0 and NULL, you now have 3 values, one of which is an UNKNOWN (NULL).
BIT values are only TRUE or FALSE, 1 or 0, ON or OFF, i.e. Binary states. Having a third state is unnatural for the system, and will continue to be a source of headaches from a maintenance standpoint.
Anyway, see if this works:
CREATE TABLE #M_Vendor (VendorID int, vendor varchar(12), contactperson varchar(12), email varchar(12), DailysettlementYN bit,phone1 varchar(12))
--Sample Data
INSERT INTO [#M_Vendor] ([VendorID],[vendor],[contactperson],,[DailysettlementYN],[phone1])
VALUES(1,'Vijay','Vijay','email',1,'1234'),
(2,'Sanjay','Sanjay','email1',0,'12345'),
(3,'Unknown','Na','Na',NULL,'122345')
DECLARE @DailySettlementYNFiler BIT
SET @DailySettlementYNFiler = NULL
DECLARE @stmnt NVARCHAR(MAX)
DECLARE @paramValue NVARCHAR(MAX)
SET @stmnt = 'SELECT * FROM #M_Vendor '
SET @paramValue = CASE WHEN @DailySettlementYNFiler = 1 THEN 'WHERE [DailysettlementYN] = 1'
WHEN @DailySettlementYNFiler = 0 THEN 'WHERE [DailysettlementYN] = 0'
ELSE ''
END
EXECUTE (@stmnt + @paramValue)
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 27, 2010 at 8:40 am
Create a "master" stored procedure. This procedure determines whether to use the parameter or not, and calls one of two appropriate procedures from there:
CREATE PROCEDURE Proc2 AS
-- procedure to run when @imported is null - select all records
SELECT *
FROM #M_Vendor;
GO
CREATE PROCEDURE Proc3 (@imported bit) AS
-- procedure to run when @imported is not null -
-- select records where DailysettlementYN = @imported
SELECT *
FROM #M_Vendor
WHERE DailysettlementYN = @imported;
GO
CREATE PROCEDURE Main (@imported bit = NULL) AS
-- main procedure - this will determine which of the child procedures to call.
IF @imported IS NULL EXECUTE Proc2
ELSE EXECUTE Proc3 @imported;
GO
EXECUTE Main @imported = 1;
EXECUTE Main @imported = 0;
EXECUTE Main @imported = NULL;
EXECUTE Main;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 27, 2010 at 1:41 pm
There is a much simpler way using COALESCE.
SELECT *
FROM #M_Vendor
WHERE Coalesce(@imported, DailySettlementYN, -1) = Coalesce(DailySettlementYN, -1)
The -1 forces an implicit conversion of the bit data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 27, 2010 at 2:19 pm
I must be missing something if this won't work:
DECLARE @imported BIT;
SELECT @imported = null;
SELECT *
FROM #M_Vendor
WHERE DailysettlementYN = @imported
OR @imported IS NULL;
Change the value of the variable, as if it were a parameter, and you get the results you're looking for.
Why all the complex proposed solutions for this? What did I miss?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 5:30 am
GSquared (12/27/2010)
I must be missing something if this won't work:
DECLARE @imported BIT;SELECT @imported = null;
SELECT *
FROM #M_Vendor
WHERE DailysettlementYN = @imported
OR @imported IS NULL;
Change the value of the variable, as if it were a parameter, and you get the results you're looking for.
Why all the complex proposed solutions for this? What did I miss?
Hi Gsquared
I think '@imported IS NULL' should be before 'DailysettlementYN = @imported'
Because if Null is passed then all rows have to return.
correct me if i am wrong
December 29, 2010 at 7:27 am
Abhijeet Chavan (12/29/2010)
GSquared (12/27/2010)
I must be missing something if this won't work:
DECLARE @imported BIT;SELECT @imported = null;
SELECT *
FROM #M_Vendor
WHERE DailysettlementYN = @imported
OR @imported IS NULL;
Change the value of the variable, as if it were a parameter, and you get the results you're looking for.
Why all the complex proposed solutions for this? What did I miss?
Hi Gsquared
I think '@imported IS NULL' should be before 'DailysettlementYN = @imported'
Because if Null is passed then all rows have to return.
correct me if i am wrong
The sequence in the Where clause doesn't matter to SQL Server. Try it both ways, you'll get the same results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 9:35 pm
Hi Gsquared
Thanks for correcting me
I was thinking the if @imported is null
so this 'DailysettlementYN = @imported' will fetch the rows containing only the rows having DailysettlementYN as NULL and so it will not go to the second condition in the OR.
It would be really nice if u can tell me why the above will not fetch the rows having DailysettlementYN as NULL
Thanks
December 30, 2010 at 7:37 am
Abhijeet Chavan (12/29/2010)
Hi GsquaredThanks for correcting me
I was thinking the if @imported is null
so this 'DailysettlementYN = @imported' will fetch the rows containing only the rows having DailysettlementYN as NULL and so it will not go to the second condition in the OR.
It would be really nice if u can tell me why the above will not fetch the rows having DailysettlementYN as NULL
Thanks
By default, Null does not equal Null. If you have ANSI NULLs turned off, which is not the default behavior, then they would be equal.
BUT, that won't matter. OR isn't exclusive. If the parameter is null, the second condition will be true regardless of what data is in the row, and it will thus return all rows.
Try it. You'll see what I mean. It's easier to understand if you see it work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2010 at 9:32 pm
Hi Gsquared
Thank you very much for the explanation.
January 3, 2011 at 6:28 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 12:46 pm
It works, many thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply