December 9, 2020 at 11:46 am
Hi there
I have a table containing a set of records which has a field called [SeasonalAddressID]
This is not always populated
now I want to pass in a bit parameter called @SeasonalAddressFlag , which when set to 1 will only return
records where [SeasonalAddressID] is populated
And when @SeasonalAddressFlag is set to 0 then I will return all records regardless of whether where [SeasonalAddressID] is populated
However I am stuck in using this BIT field in the where clause!
here is my DDL used to recreate this scenario
-- DROP TEMP TABLE IF ALREADY EXISTS
If OBJECT_ID(N'tempdb..#OutputTable', N'U') IS NOT NULL DROP TABLE #OutputTable
/*************************************************************************************************
TEMP TABLE CREATION
*************************************************************************************************/
CREATE TABLE [#OutputTable](
[SeasonalAddressID] INT NULL,
[AirlineID] INT NULL,
[OffID] INT NULL
)ON [PRIMARY]
insert into [#OutputTable] (SeasonalAddressID,AirlineID, offid)
select NULL as SeasonalAddressID, 1044360805 as AirlineID, 11602 as offid
union all
select 103858102 as SeasonalAddressID, 1052037087 as AirlineID, 1602 as offid
union all
select NULL as SeasonalAddressID, 1052067646 as AirlineID, 1602 as offid
union all
select 103858114 as SeasonalAddressID, 1070904138 as AirlineID, 1602 as offid
select * from [#OutputTable]
declare @SeasonalAddressFlag bit =1
select * from #OutputTable
where SeasonalAddressID
case when (@SeasonalAddressFlag = 1) then SeasonalAddressID is not null
else SeasonalAddressID
end
December 9, 2020 at 12:11 pm
The below query will produce your desired result:
select *
from #OutputTable
where coalesce(SeasonalAddressID, -1) = case
when (@SeasonalAddressFlag = 1)
then SeasonalAddressID
else coalesce(SeasonalAddressID, -1)
end
December 9, 2020 at 3:15 pm
Honestly, I would create three procedures. One to take the flag and test for its value. That in turn would call one of two other procs. The reason is that the cached plan will probably not be the best plan for one of the values in the flag.
If it's 1, it will be a good plan, if it's 2, it will not be a good plan. Or visa-versa.
If it was me, I would probably write 2 separate procs, forget about the flag, and call the appropriate one from the UI in the code.
I would also test the code provided by HanShi for performance.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2020 at 4:37 pm
NEVER use ISNULL() in a WHERE or JOIN.
Instead, in this specific case, use this method:
select * from #OutputTable
where (@SeasonalAddressFlag = 0 OR SeasonalAddressID IS NOT NULL)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 10, 2020 at 2:39 am
>> I have a table containing a set of records [sic] which has a field [sic] called seasonal_address_id. This is not always populated. <<
You get a chance, please read the standards. Rows are not records. A field is a part of a column that still has meaning. For example, a date column is made up of ( year, month, day), But each field is Incomplete by itself.
>> Now I want to pass in a BIT parameter called @seasonal_address_flag, which when set to 1 will only return records [sic] where seasonal_address_id is populated <<
Bit parameters are used in assembly language, but we don’t like to use them in RDBMS. We preferred having predicates (actually in SQL there called “search conditions”).
>> And when @seasonal_address_flag is set to 0 then I will return all records [sic] regardless of whether where seasonal_address_id is populated. <<
Have you ever had a course in basic software engineering? The two major concepts are coupling and cohesion. Coupling has to do with how tightly the modules fit together. We would like each module to be as independent of the others as possible. The best form of coupling are mathematical functions that can be nested inside each other. Cohesion refers to how well a module of code performs a single task. We do not like to have a “automobiles, squids, and Lady Gaga” function in a well-designed system. You are describing a system with what is called flag coupling and it’s considered bad design.
DDL you did post has serious problems. Did you know that by definition – not as an option – a table must have a key? But what you did post since all the columns can be null can never have a key. Essentially, it’s a deck of punch cards, not a relational table. Next identifiers exist on a nominal scale; this means they have to be character strings and not numerics! You really believe that this the square root of your credit card number is meaningful? Finally, you didn’t bother to do enough research to find out that there are three digit airline codes; I did not bother to do a reference over to a table of them, or even put in a check constraint to guarantee that they at least have the correct format. It took me 15 seconds to find these codes ( https://www.kwe.co.jp/en/useful-contents/code1). Did you even bother to look for them?
Since there’s no such thing as a output table in RDBMS, I just decided to use the Dummy name foobar for the table. Please replace it with something meaningful.
CREATE TABLE Foobar
(seasonal_address_id CHAR(30),
airline_code CHAR(3) NOT NULL,
office_id CHAR(4) NOT NULL,
PRIMARY KEY (airline_code, office_id) --- my best guess!
);
I’m still trying to figure out why you would use a table creation, syntax that was created over 50 years ago by Sybase in the first version of SQL Server. Microsoft has had the ANSI/ISO standard table constructors for quite some time now
INSERT INTO Foobar
VALUES
(NULL, ‘1044360805’, ‘1602’),
(‘103858102’, ‘1052037087’, ‘1602’),
(NULL, ‘1052067646’, ‘1602’),
(‘103858114’, ‘1070904138’, ‘1602’);
Your block of code is totally absurd. CASE is not a flow control command in SQL! SQL is a declarative language and has no control flow. CASE is an expression in SQL. It must return a single scalar value of a known data type.Please consider how absurdly redundant that non-relational flag is
SELECT seasonal_address_id, airline_code, office_id FROM Foobar;
WHERE seasonal_address_id IS NULL;
SELECT seasonal_address_id, airline_code, office_id FROM Foobar;
WHERE seasonal_address_id IS NOT NULL;
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply