Convert EXEC to script

dbForge Studio for SQL Server simplifies inlining and debugging of SQL code.

To use the Convert EXEC to script feature, right-click the procedure name and select Refactoring > Convert EXEC to Script.

Inline stored procedures and scalar functions

dbForge Studio replaces the calls to a stored procedure or a scalar function in EXEC statements with the actual SQL code from the referenced procedure or function.

Example

Convert the following statement into a script:

EXEC HumanResources.uspUpdateEmployeePersonalInfo @BusinessEntityID = 0
                                                , @NationalIDNumber = N''
                                                , @BirthDate = '2020-07-27 17:35:44.172'
                                                , @MaritalStatus = N''
                                                , @Gender = N''

Right-click the procedure name and select Convert EXEC to Script. In the same document, the following script appears:

DECLARE @BusinessEntityID INT = 0
      , @NationalIDNumber NVARCHAR(15) = N''
      , @BirthDate DATETIME = '2020-07-27 17:35:44.172'
      , @MaritalStatus NCHAR(1) = N''
      , @Gender NCHAR(1) = N'';
BEGIN
        SET NOCOUNT ON;

        BEGIN TRY
                UPDATE [HumanResources].[Employee]
                SET [NationalIDNumber] = @NationalIDNumber
                  , [BirthDate] = @BirthDate
                  , [MaritalStatus] = @MaritalStatus
                  , [Gender] = @Gender
                WHERE [BusinessEntityID] = @BusinessEntityID;
        END TRY
        BEGIN CATCH
                EXECUTE [dbo].[uspLogError];
        END CATCH;
END;

Unwrap dynamic SQL into parameterized queries

For application-generated SQL using sp_executesql, the Studio removes surrounding apostrophes and converts inline EXEC statements into readable scripts.

Example

Convert the following statement into a script:

EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorks2019.HumanResources.Employee   
          WHERE BusinessEntityID = @level',  
          N'@level tinyint',  
          @level = 108;

Right-click the procedure name and select Convert EXEC to Script. In the same document, the following script appears:

DECLARE @level TINYINT;
SET @level = 108;
SELECT *
FROM AdventureWorks2019.HumanResources.Employee
WHERE BusinessEntityID = @level