Substitute Values

When enabled, substitutes values in SQL Command dynamically.

Stored procedure name for retrieving parameter values is “Stored Procedure Prefix”_SubstituteValues_Proc.

In the below example @order_id, @order_date, @order_description are substituted for the first statement and @order_id is substituted for the second statement.

The below example uses “Data Source=SQLWorkshops\SQL2012;Database=test;Integrated Security=true” as “Connection String” and “SQLTest1” as “Stored Procedure Prefix” in test database.

SQL Command:

use tempdb
insert into orders values (@order_id, '@order_date', '@order_description') --BEEE1DDD-3397-4494-8D12-90CA679C8240
go
use tempdb
select * from orders where order_id = @order_id --C91BAD2E-0EAD-4A11-914C-EB485B1F582E
go

SQLTest1_SubstituteValues_Proc:

use test
go
drop proc SQLTest1_SubstituteValues_Proc
go
drop table SQLTest1_SubstituteValues_Log_Table
go
create table SQLTest1_SubstituteValues_Log_Table (id int identity primary key, ctime datetime, SQLTestInstanceGUID uniqueidentifier, SQLTestWorkloadGUID uniqueidentifier, SQLTestWorkloadName nvarchar(256), Workload int, Connection int, ConnectionString nvarchar(max), Thread int, BatchIndex int, CommandText nvarchar(max), Iteration int)
go
create proc SQLTest1_SubstituteValues_Proc (@SQLTestInstanceGUID uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @Workload int, @Connection int, @ConnectionString nvarchar(max), @Thread int, @BatchIndex int, @CommandText nvarchar(max), @Iteration int)
as
begin
    insert into SQLTest1_SubstituteValues_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, Workload, Connection, ConnectionString, Thread, BatchIndex, CommandText, Iteration) select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @Workload, @Connection, @ConnectionString, @Thread, @BatchIndex, @CommandText, @Iteration
    if(@Workload = 1 and @Connection = 1 and @Thread = 1 and @CommandText like '%BEEE1DDD-3397-4494-8D12-90CA679C8240%')
    begin
        select top 100 row_number() over(order by t1.column_id) as order_id, getdate() as order_date, 'Sample order description' as order_description from sys.all_columns t1 cross apply sys.all_columns t2
    end
    else if(@Workload = 1 and @Connection = 1 and @Thread = 1 and @CommandText like '%C91BAD2E-0EAD-4A11-914C-EB485B1F582E%')
    begin
        select top 100 row_number() over(order by t1.column_id) as order_id from sys.all_columns t1 cross apply sys.all_columns t2
    end
end
go
select * from SQLTest1_SubstituteValues_Log_Table
go

Table to create on the target server:

use tempdb
if (object_id('orders') is not NULL)
    drop table orders
create table orders (order_id int, order_date datetime, order_description nvarchar(2000))
go

Please help us improve this page by entering your comments and suggestions below:


  • Captcha image