Parameterize Query

When enabled, parameterizes the SQL Command.

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

In the below example @order_id, @order_date, @order_description are parameterized for the first statement and @order_id is parameterized 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)
go
use tempdb
select * from orders where order_id = @order_id
go

SQLTest1_ParameterValues_Proc:

use test
go
drop proc SQLTest1_ParameterValues_Proc
go
drop table SQLTest1_ParameterValues_Log_Table
go
create table SQLTest1_ParameterValues_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_ParameterValues_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_ParameterValues_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 @BatchIndex = 1)
    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 @BatchIndex = 2)
    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_ParameterValues_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