Problem Definition

Recently, there was a thread in the forums asking whether there is any way to avoid the dynamic SQL in the generated code while scripting out the stored procedures from SQL Server Management Studio (SSMS). This wiki article discusses a possible solution for the issue.




Steps To Reproduce the Problem

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to your database
  3. Choose Tools -> Options from top menu and make sure Include IF NOT EXISTS clause option is set to True in scripting properties
  4. Open Object Explorer
  5. Expand ServerName -> DatabaseName -> Programmability -> StoredProcedures
  6. Choose your stored procedure to script out as below
  7.                  
  8. You will be provided with script as below                                            
  9. As you see the generated script would involve a dynamic SQL part as a call to the system stored procedure sp_executesql



Reason

The main reason for the above issue is because of the setting we did in the scripting properties to include IF NOT EXISTS clause. But this is required in the script to ensure a smooth migration across environments i.e from development to QA to production while doing incremental changes.




Resolution

This issue can be resolved by using the below steps instead for scripting out the objects

  1. Choose Tools -> Options from top menu and make sure Include IF NOT EXISTS clause option is set to False in scripting properties

  2. Expand ServerName -> DatabaseName -> Programmability -> StoredProcedures
  3. Choose script out option as below                                           
  4. You will now see that the generate script does not have dynamic SQL part as per below
  5. If you need to do scripting for multiple objects simultaneously you can use object explorer detail window as below
  6. You will get script for all objects included in a single file as below



Summary

As shown by the above steps, we can make use of Script As -> DROP And CREATE option to avoid generating scripts with dynamic SQL for stored procedures.


See Also

Object Filtering Using Object Explorer