Adam Machanic was reviewing code from stored procedure and noticed that there a lot of flow control logic with repetitive SELECT statements ... 

IF @Access_Code = 0
  IF @Archive_Desc IS NULL
    SELECT * FROM [Archive_Letters]
  ELSE
    SELECT * FROM [Archive_Letters]
     WHERE [Archive_Desc] IS NULL

/* Snip 10 Similar Cases */

ELSE
  IF @Archive_Desc IS NULL
    SELECT * FROM [Archive_Letters]
     WHERE [Access_Code] = @Access_Code
  ELSE
    SELECT * FROM [Archive_Letters]
     WHERE [Access_Code] = @Access_Code
       AND [Archive_Desc] IS NULL

In order to make the code more maintainable, Adam asked the developer to use dynamic SQL. The developer was unfamiliar with this, so Adam explained that he simply needed to build a string that contained the SELECT statement, and then use sp_executesql to execute that statement. This way, Adam explained, all the common parts (the SELECT and FROM clauses) could be kept common and all the selection logic kept in the branching logic.

Adam wasn't quite sure the developer understood, so he made sure to send him an article and showed him some additional examples by email. At the next code review, Adam was presented with the new, dynamic version of the code ... 

IF @Access_Code = 0
  IF @Archive_Desc IS NULL
    @sql = 'SELECT * FROM [Archive_Letters]'
    EXEC sp_executesql @sql
  ELSE
    @sql = 'SELECT * FROM [Archive_Letters]
     WHERE [Archive_Desc] IS NULL'
    EXEC sp_executesql @sql

/* Snip 10 Similar Cases */

ELSE
  IF @Archive_Desc IS NULL
    @sql = 'SELECT * FROM [Archive_Letters]
     WHERE [Access_Code] = ' + CAST(@Access_Code AS VARCHAR(5))
    EXEC sp_executesql @sql
  ELSE
    @sql = 'SELECT * FROM [Archive_Letters]
     WHERE [Access_Code] = ' + CAST(@Access_Code AS VARCHAR(5)) + '
       AND [Archive_Desc] IS NULL'
    EXEC sp_executesql @sql
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!