How to convert EXEC to script

Last modified: September 9, 2024

Convert the execution of stored routines into a script

SQL Complete can inline stored procedures and scalar functions in EXEC statements within the query window. With the Convert inline EXEC to script feature, the evocation of a stored procedure or a scalar function can be replaced with the inline code it contains.

EXEC to script

Example

Let’s take a closer look at how it works. For instance, here is a statement:

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

Point the cursor to the procedure name (e.g. uspUpdateEmployeePersonalInfo), right-click on the query window, 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

There are cases when it is required to debug app-generated SQL. When it comes to parameterized queries based on sp_executesql, SQL Complete can be used to quickly eliminate apostrophes around the SQL and convert inline Exec To script.

Inline EXEC

Example

Let us illustrate this feature with the following example:

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

Point the cursor to the procedure name (e.g. sp_executesql), right-click on the query window, 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

Want to find out more?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by SQL Complete.

All features

All features

Get acquainted with the rich features and capabilities of the tool in less than 5 minutes.

Request a demo

Request a demo

If you consider employing this tool for your business, request a demo to see it in action.

SQL Complete logo

Ready to start using  

SQL Complete?

Download a FREE 14-day trial