The funny WHERE

Question

The funny WHERE

Viewing 2 posts - 16 through 16 (of 16 total)

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

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

Solomon Rutzky - Monday, November 26, 2018 9:41 AM
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...

Hehe I would never have chosen to use a binary collation for how we use this. It is a serious PITA!!! Not from the code side but from the usage side. This is our ERP and "simple" like looking for a customer or an email is horrific because Chad <> chad <> CHAD and the front end does nothing to make this less user hostile. We don't currently run into the diacritic issues described in your blog post but functionally the case sensitive nature of searching is horrible. And since it isn't our code we can't make it any better.

_______________________________________________________________

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/

t.ovod-everett Default port Points: 1487 More actions · Answer 2

Eirikur Eiriksson - Sunday, November 25, 2018 8:26 AM
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!
😎

Also, your DBA mentions that it might be worth investigating the performance impacts of adding OPTION (RECOMPILE) to that query in the stored procedure (especially in a scenario where the optionally filtered column is highly selective).