The funny WHERE

Question

Post reply

The funny WHERE

Steve Jones - SSC Editor

SSC Guru

Points: 736255
More actions
November 24, 2018 at 6:41 pm

#77378

Comments posted to this topic are about the item The funny WHERE

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

November 25, 2018 at 12:03 am

#2014436

Good question, thanks Steve.

...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Great question. Although preferable, this is one of the reasons why you "must look eye" when variables and columns are named the same.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

Should say: I get all rows for all statuses where oh.OrderTotal > 0

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Jonathan AC Roberts - Sunday, November 25, 2018 8:02 AM
Should say: I get all rows for all statuses where oh.OrderTotal > 0

My thought exactly!
😎

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 5

Nice one to start the week on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

david.gugg SSCertifiable Points: 6096 More actions · Answer 6

Jonathan AC Roberts - Sunday, November 25, 2018 8:02 AM
Should say: I get all rows for all statuses where oh.OrderTotal > 0

Yep, this had me staring at the question for a minute trying to figure out a correct answer.

Be still, and know that I am God - Psalm 46:10

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Sean Lange - Monday, November 26, 2018 8:18 AM
Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.

Slightly envious Sean, having a "primary" database is really a luxury these days 😀
😎

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Eirikur Eiriksson - Monday, November 26, 2018 8:28 AM
Sean Lange - Monday, November 26, 2018 8:18 AM
Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.
Slightly envious Sean, having a "primary" database is really a luxury these days 😀
😎

haha. Probably not all that well worded. The database with the most activity would be more correct.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 10

Sean Lange - Monday, November 26, 2018 8:37 AM
haha. Probably not all that well worded. The database with the most activity would be more correct.

Legacy systems and DBAs with one database, been there but so long ago, not certain that all the dinosaurs had even turned into oil by then,
😎

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 11

Sean Lange - Monday, November 26, 2018 8:18 AM
Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.

To clarify (not sure if you meant "database" literally, or meaning the instance/server): there would be a parse-error on Instances where the Instance-level Collation is either case-sensitive or binary. This is due to declaring the variable as "@status" but referencing it in the WHERE clause as "@Status". Individual DB default Collations are irrelevant here since variable names are controlled by the Instance-level Collation.

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Solomon Rutzky - Monday, November 26, 2018 9:15 AM
Sean Lange - Monday, November 26, 2018 8:18 AM
Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.
To clarify (not sure if you meant "database" literally, or meaning the instance/server): there would be a parse-error on Instances where the Instance-level Collation is either case-sensitive or binary. This is due to declaring the variable as "@status" but referencing it in the WHERE clause as "@Status". Individual DB default Collations are irrelevant here since variable names are controlled by the Instance-level Collation.
Take care, Solomon...

Well that is interesting. Didn't realize that but makes sense. The instance collation is binary as is each database on the instance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 13

nice and easy one to start the week,
ta

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 14

Sean Lange - Monday, November 26, 2018 9:21 AM
Solomon Rutzky - Monday, November 26, 2018 9:15 AM
Sean Lange - Monday, November 26, 2018 8:18 AM
Or an error in our primary database since it is case sensitive. 🙂 Good question Steve.
To clarify (not sure if you meant "database" literally, or meaning the instance/server): there would be a parse-error on Instances where the Instance-level Collation is either case-sensitive or binary. This is due to declaring the variable as "@status" but referencing it in the WHERE clause as "@Status". Individual DB default Collations are irrelevant here since variable names are controlled by the Instance-level Collation.
Take care, Solomon...
Well that is interesting. Didn't realize that but makes sense. The instance collation is binary as is each database on the instance.

Ah, so to further clarify (since you are equating "binary" and "case-sensitive" collations), please see my post: No, Binary Collations are not Case-Sensitive

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR