Conditional 'WHERE' clause based on BIT value

Transact-SQL
1

I am working on a query whose where clause varies depending on a BIT value. Basically, I am listing stock records and if @IncludeAccessories = 1 then I want to list ALL stock records but if set to 0 then I want to exclude stock belonging to StockGroup D.

Please see example below. Can anyone help?

DECLARE @STOCK TABLE (StockNo Varchar(20),StockGroup VARCHAR(1))

DECLARE @IncludeAccessories BIT
SET @IncludeAccessories = 1

INSERT INTO @STOCK
SELECT 'A001', 'A'
UNION ALL
SELECT 'A002', 'A'
UNION ALL
SELECT 'A003', 'B'
UNION ALL
SELECT 'A004', 'B'
UNION ALL
SELECT 'A005', 'B'
UNION ALL
SELECT 'A006', 'C'
UNION ALL
SELECT 'A007', 'C'
UNION ALL
SELECT 'A008', 'D'

SELECT * FROM @STOCK
WHERE StockGroup IN (CASE WHEN @IncludeAccessories = 1 THEN ....????

2

SELECT * FROM @STOCK
WHERE StockGroup = (CASE WHEN @IncludeAccessories = 1 THEN
StockGroup
ELSE
case when StockGroup='D' then
null
else StockGroup end
END )

3

Thanks for this. That makes complete sense. One more thing; if there were many StockGroups classed as attachments, how could this be handled? Originally, i thought this could be the case, hence assuming i need to do
SELECT * FROM @STOCK
WHERE StockGroup IN ... rather than
SELECT * FROM @STOCK
WHERE StockGroup =

(and sorry for moving the goal post :smile:)

4

Hi

I have a slightly different approach

SELECT * FROM @STOCK
WHERE StockGroup IN (CASE WHEN @IncludeAccessories = 1 THEN StockGroup END)
AND StockGroup NOT IN (CASE WHEN @IncludeAccessories = 0 THEN 'D' ELSE StockGrup END)

5

Hi

that should be OR

SELECT *
FROM @STOCK
WHERE StockGroup IN (
		CASE 
			WHEN @IncludeAccessories = 1
				THEN StockGroup
			END
		)
	OR StockGroup NOT IN (
		CASE 
			WHEN @IncludeAccessories = 0
				THEN 'D'
			ELSE StockGrup
			END
		)
1 Like
6

Perfect. Thanks for your help!

Nev.

7

I'm being thick - how does IN / NOT IN help here?

SELECT * 
FROM @STOCK
WHERE
(
	   @IncludeAccessories = 1		-- Include everything
	OR StockGroup NOT IN ('D', 'DDDDD')	-- If several ignore groups
)
8

Hi Please note @IncludeAccessories = 0
for NOT IN

9

don't use IN/NOT IN when only testing one value. Use =/<>

1 Like
10

Yeah, that IS what my query done :smile:

11

I am working on a query whose where clause varies depending on a BIT value.

I hope not. We do not use BIT flags in a declarative language like SQL; that was 1960's assembly language! But more than that, read any book on Software Engineering. This is called “flag coupling” and it is the worst way to write code in any language. We make fun of this with the phrase “Automobiles, Squids and Lady Gaga” code. Did you read Yourdon, DeMarco, et al or anything about Software Engineering in school?

Google coupling and cohesion.

Basically, I am listing stock records [sic: rows are not records] and if @Include_Accessories = 1 then I want to list ALL stock records [sic] but if set to 0 then I want to exclude stock belonging to Stock_Group D.<<

Why don't You know that a rows is not a record? That is the first week of an SQ class when they discuss the basic concepts of RDBMS.

Also, where is the DDL? This is minimal Netiquette in SQL forums. What you posted is garbage, without any keys, constraints, etc.

A variable length stock number is a bad design and a NULL-able stock number is insane. You should be using an industry standard code if possible (EAN, GTIN, UPC, etc).

Think about how silly VARCHAR(1) is.

CREATE TABLE Stock
(stock_nbr CHAR(4) NOT NULL PRIMARY KEY,
CHECK (stock_nbr LIKE 'A[0-9][0-9][0-9]'),
stock_grp CHAR(1) NOT NULL
CHECK (stock_grp LIKE '[A-Z]')
)

See how we have a key and use constraints in SQL? Here is the ANSI/ISO Standard insertion syntax that T-SQ has had for a long; there is no need to use the old Sybase stuff today

INSERT INTO Stock
VALUES ('A001', 'A'),
('A002', 'A'),
('A003', 'B'),
('A004', 'B'),
('A005', 'B'),
('A006', 'C'),
('A007', 'C'),
('A008', 'D');

Now write two views. Think in sets, not procedures.

CREATE VIEW All_Stock
AS
SELECT stock_nbr, stock_grp
FROM Stock;

CREATE VIEW Non_Accessories_Stock
AS
SELECT stock_nbr, stock_grp
FROM Stock
WHERE stock_grp <> 'D';

12

Even people who have read (and even written) the gospels sometimes make mistakes. To err is human, so be divine and forgive. :smiley: :smiley: :smiley: :smiley: :smiley: :smiley:

BTW, code formatting can be done using the </> button on the edit window's menu bar.

2 Likes
13

Records are the physical storage for data, made up of fields. Rows are the logical unit is RDBMS. For example, on a RAID storage device, the same row can be in many records. Or the row can be virtual and have no record at all.

When you confuse rows and records, your mindset gets stuck in a physical model and you design a 1960's file system instead of a relational database.

For example. materializing data on the disk as if we were still using mag tapes and punch cards. it is faster and smaller to use computed columns.

14

You are quite wrong. Bits are used everywhere in SQL Server. Take a look at sys.databases for example. Out of 68 columns, 42 of them are bits.

Also, the OP uses a bit to indicate that his code should return a different rowset (with/without all stock records). This is perfectly acceptable. Could he do it with views? Perhaps. It depends. I can think of one concrete example where that won't work very well:

Suppose you are writing an SSRS report with parameters prompted at run time. In the OPs situation, one parameter is the choice between all stock records or a subset. The report uses one or more datasets (SSRS terminology) and each dataset can have one and only one query. Somewhere in your report, you will need to handle that flag. If it is truly boolean, the simplest for development and maintenance is to pass the flag to a stored procedure (or tvf, if you like) that incorporates that flag into the query, much as has been suggested.

I may be the only one but I find your sarcastic tone to be insulting, offensive, counter-productive and entirely out of place.

1 Like
15

I am in agreement with all the points you are making regarding the difference between rows and records.

However, we have to also keep in mind that many of the people who ask questions on the forum are those who are starting out with databases, or perhaps don't even want to start with databases, but are somehow forced into doing something with the databases. So it is not unexpected that they don't know the difference between a row and record, or a field and a column.

While I do appreciate your efforts to correct the confusion between rows and records, when someone asks a question, dwelling on the row vs record error may not be as useful as providing them with the answers they want and then perhaps pointing out the distinction for future reference. In your reply, you did provide very cogent answers, but somehow the tone of the reply seemed to take away something from the quality of the response.

3 Likes
16

Suppose you are writing an SSRS report with parameters prompted at run time. In the OPs situation, one parameter is the choice between all stock records or a subset. The report uses one or more datasets (SSRS terminology) and each dataset can have one and only one query. Somewhere in your report, you will need to handle that flag. If it is truly boolean, the simplest for development and maintenance is to pass the flag to a stored procedure (or tvf, if you like) that incorporates that flag into the query, much as has been suggested.
[/quote]

That's very perceptive. I work for a software company that sells an SQL based product and my role involves developing reports (using Crystal Reports) for our users. In many cases, I am required to write SQL views or procs to use as the data source.
In this particular case, I wanted the end user to be prompted with a number of runtime parameters, one being "Include Accessories?".
The script I originally posted was simply put together to convey my problem, bore no resemblance to our actual database in any shape or form and achieved the desired end outcome, that is, to be provided with a solution that worked.
With regard to the post from jcelko, I believe he is entitled to share his opinion and clearly feels the need to exhibit his knowledge in this field. However, I believe the tone is, without a doubt, insulting and attempts to practise condescension.
I am new to this forum and, so far, it has been an invaluable resource.
Thanks for your support.

17

No you aren't the only one. Celko is rude to everyone in all the forums that he responds in. He was banned from this one back in 2011 and I presume it was by unfortunate accident that his account was migrated to the new forum.

For example see Russels reply in this, old, thread from the time that Celko was banned previously
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164187#640232

I'm not. I am quite happy that people ask questions here and interchange the use or Rows and Records, Columns and Fields, I'm perfectly capable of understanding what they mean or, if not, asking them for clarification. I'm also happy to steer Newbies towards improving their skills, normalising their duff database designs, and telling them why using NOLOCK is a bad idea without the O/P having to first go to night-school to study Database Theory and without calling them a moron.

I read, and owned, many of Celko's books and back then thought he was a rock star. Now I have no respect for him at all and I find it a great pity, and sad, that he doesn't use his enormous experience and skill to better effect.

Here's an abridged screenshot of threads on SQL Server Central yesterday - none of Celko's posts still exists, presumably all abusive and all deleted by the MODs there. What a pain for them to have to spend their time dealing with.

1060Ă—424 76.1 KB

I found a reply that quoted Celko's original message and was thus still visible, Same arrogant put-down of the O/P; here's a copy you can judge for yourself:

I need some help building a query. I have a table with 4 columns and need to try and put the times together.

And thanks to your bad manners, we have no DDL! The ASCII picture you did post has every column wrong. The temporal model is also wrong; a (start_timestamp, end_timestamp) interval pair is never split over multiple rows. And we use ISO-8601 temporal format, not that silly local dialect you had. You used AM-PM in the 21-st century! You failed to use ISO-8601 dates!

There are some inconsistencies with this, and I'm hoping to exclude them. Here is a sample table:

So you admit to making us work with garbage data! So polite of you! This is the idiom for temporal data, I am not going to your job for you; give us the data and a spec, and we will help you.

CREATE TABLE Garbage
(emp_id CHAR(1) NOT NULL,
event_type SMALLINT NOT NULL -– bad choice of data type
CHECK (event_type IN (1,2)),
in_something_timestamp DATETIME2(0) NOT NULL,
out_something_timestamp DATETIME2(0), -- null is still active
CHECK (in_something_timestamp < out_something_timestamp)
);

Basically what I need to do is take the time from rows with non_relational_function_flg 1 and match it with non_relational_function_flg 2 so I can get a total time of the clock in. <<

NO! They are part of a single interval data type.

I'm basically trying to get totals for each Clock In-Out type. I hope I made some sense with this.

No, but it tells us that you never read a single book on SQL, Data modeling or RDBMS. The INTERVAL data type is a fundamental concept. Please stop programming until you have the fundamental concepts; you are dangerous to your employer.

Did your boss do this to you? You can only fail. It is time to quit and update the resume.