question

dwcarr89 avatar image
dwcarr89 asked

Short hand for same CASE WHEN statement

Hi there - I have a query with multiple CTE tables all with the same, very long CASE WHEN statement. I am wonder if there is some sort of short hand / way to reference the case when statement so that I don't have to 1) type out the whole thing every time 2) edit every case when every time something changes and 3) just make the query look cleaner. Let me know if there is any way to do this!
case-statementcase-expression
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@dwcarr89 There is no any short hand for case statement per se, but it is occasionally possible to restate the query in such a way that the repetitive case statements are outsourced to yet another CTE. This may make the query look cleaner because there is but only one long case statement instead of multiple repetitions of the same, but will probably NOT make it any faster. The machines have no problems with parsing long, complex case statements without slowing down a bit, so there is no performance benefit from restating the query to reduce the line count and/or number of times the case statement is spelled out repetitively, but there certainly is a benefit for us, the wimps (a.k.a. humans :) ), any time we can make the query look simpler and easier to maintain. Please post your query as an attachment to your question (cleanup/obfuscate the object names if necessary), maybe someone can come up with the solution to reduce the number of repetitive case statements.
1 Like 1 ·
@dwcarr89 One way I can think of off the top of my head would be to Declare a variable and set the variable to resemble your Case statement e.g Declare @Casestmt Varchar(4000) SET @Casestmt = 'Case When...' (include the whole clause in the set) the encapsulate your query with apostrophes to make the entire query a string, replace ll existences of the case statement with the variable @Casestmt and execute the entire statement this way inside of an exec, the downside is the possibility of sql injection, as @oleg has advised, it's worth posting your query and then it helps to see what options are available based on your code.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.