Using CASE to create SQL Statement

Question

Using CASE to create SQL Statement

Viewing 3 posts - 16 through 17 (of 17 total)

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

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

ChrisM@Work (7/2/2012)
Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite a clever way of making case somewhat function like a programming block. The downside is the performance of that is going to suffer greatly and tracking it down will become painful. Not to mention that code will be a lot harder to maintain because dynamic sql is just harder to read when it gets complicated like that. Just my 2¢.
Are you sure, Sean? I totally agree with debugging and maintenance being a pain in the a$$ but I'm not so sure about performance.

Well I should be more careful about comments that when read sound more absolute than my intention. 😉 I should say it has the potential of becoming a performance issue. And if it does suffer from poor performance, tuning it could be very difficult. Thanks for making me clarify Chris.

_______________________________________________________________

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Sean Lange (7/2/2012)
ChrisM@Work (7/2/2012)
Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite a clever way of making case somewhat function like a programming block. The downside is the performance of that is going to suffer greatly and tracking it down will become painful. Not to mention that code will be a lot harder to maintain because dynamic sql is just harder to read when it gets complicated like that. Just my 2¢.
Are you sure, Sean? I totally agree with debugging and maintenance being a pain in the a$$ but I'm not so sure about performance.
Well I should be more careful about comments that when read sound more absolute than my intention. 😉 I should say it has the potential of becoming a performance issue. And if it does suffer from poor performance, tuning it could be very difficult. Thanks for making me clarify Chris.

No worries Sean - excellent reply 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite a clever way of making case somewhat function like a programming block. The downside is the performance of that is going to suffer greatly and tracking it down will become painful. Not to mention that code will be a lot harder to maintain because dynamic sql is just harder to read when it gets complicated like that. Just my 2¢.

I'm glad you think it was clever (:blush:) but in truth I was just messin' around.

I'd probably do it with the IF/ELSE IF construct.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St