SQL – How to call a part of query conditionally

I need to write a SQL query like this:

do step1

do step2

do step3 – this step does a lot of stuffs: define variables, use some variables defined in step 1, query a lot of data into temporary table…

if(cond1)

   if(cond2)        
 
       Begin            
          do step 4             
          call step 3 here      
       End  
    else
    
      Begin             
           do step 5        
      End  
  else  
 
  Begin         
     call step 3 here   
  End

How to make step 3 a reusable query to avoid calling step 3 unnecessarily? notice that step 3 should be a part of the whole query

When I try to create a @step3Query NVARCHAR(MAX), SET @step3Query = ‘…’ Then call this query in appropriated places by call “EXEC sp_executesql @step3Query”. But I got a lot of errors, and I am not sure that is a correct way to do task like that. Any suggestions would be highly appreciated.

Answer

Here is one method that could work (depending on your exact circumstance). The main thing for this is that it’s easy, as well as easy to read.

You could have 3 variables to use as flags e.g., @Query3_Flag, @Query4_flag, @Query5_flag.

Your conditional checks just set these flags e.g.,

IF (condition1)
    BEGIN
    SET @Query4_Flag = 1;
    SET @Query3_flag = 1;
    SET @Query5_flag = 0;
    END

Before each of queries 3, 4 and 5, have the IF statement check the flag and only run the query if the flag is set to 1 e.g.,

IF @Query3_Flag = 1 
    BEGIN
    (run Query 3)
    END

Note that the queries will need to be in the correct order.