Home Re-use Query Parts in T-SQL

# Re-use Query Parts in T-SQL

Berin Loritsch
1#
Berin Loritsch Published in 2017-11-14 17:09:23Z
 I have a very complicated stored procedure that repeats a very complicated query with different where clauses based on certain values passed in. The stored procedure takes up over 500 lines of code, with the common part of the query taking up just over 100 lines. That common part is repeated 3 times. I originally thought to use CTE (Common Table Expressions) except in T-SQL you can't define the common part, do your IF statement and then apply the WHERE clause. That's essentially what I need. As a workaround I created a view for the common code, but it's only used in one stored procedure. Is there any way to do this without creating a full view or temp tables? Ideally I would like to do something like this: WITH SummaryCTE (col1, col2, col3...) AS ( SELECT concat("Pending Attachments - ", ifnull(countCol1, 0)) col1 -- all the rest of the stuff FROM x as y LEFT JOIN attachments z on z.attachmentId = x.attachmentId -- and more complicated stuff ) IF (@originatorId = @userId) BEGIN SELECT * FROM SummaryCTE WHERE -- handle this security case END ELSE IF (@anotherCondition = 1) BEGIN SELECT * FROM SummaryCTE WHERE -- a different where clause END ELSE BEGIN SELECT * FROM SummaryCTE WHERE -- the generic case END  Hopefully the pseudo code gives you an idea of what I would like. Right now my workaround is to create a view for the contents of what I defined SummaryCTE as, and then handle the IF/ELSE IF/ELSE clause. Executing this structure will throw an error at the first IF statement because the next command is supposed to be a SELECT instead. At least in T-SQL. Maybe this doesn't exist in any other way, but I wanted to know for sure.
Becuzz
2#
 Well, aside from the temp tables and views that you've identified, you could go with dynamic SQL to build the code then execute it. This keeps you from having to repeat code, but makes it a bit hard to just deal with. Like this: declare @sql varchar(max) = 'with myCTE (col1, col2) as ( select * from myTable) select * from myCTE' if (@myVar = 1) begin @sql = @sql + ' where col1 = 2' end else if (@myVar = 2) begin @sql = @sql + ' where col2 = 4' end -- ... exec @sql  Another option would be to incorporate your different where clauses into the original query. WITH SummaryCTE (col1, col2, col3...) AS ( SELECT concat("Pending Attachments - ", ifnull(countCol1, 0)) col1 -- all the rest of the stuff FROM x as y LEFT JOIN attachments z on z.attachmentId = x.attachmentId -- and more complicated stuff ) select * from SummaryCTE where ( -- this was your first if @originatorId = @userId and ( whatever you do for your security case ) ) or ( -- this was your second branch @anotherCondition = 1 and ( handle another case here ) ) or -- etc. etc.  This eliminates the if/else chain but makes the query more complicated. It also can cause some bad cached query plans because of parameter sniffing, but that may not matter much depending on your data. Test that before making a decision. (You can also add optimizer hints to not cache the query plan. You won't get a bad one, but you also take a hit on every execution to create the query plan again. Test to find out, don't guess. Also, a solution with a view and the if/else chain will suffer from the same parameter sniffing/cached query plan problem.)