Parameterize an SQL IN Clause

Parameterize an SQL IN clause

Here's a quick-and-dirty technique I have used:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

So here's the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Two caveats:

• The performance is terrible. LIKE "%...%" queries are not indexed.
• Make sure you don't have any |, blank, or null tags or this won't work

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

Parameters in query with in clause?

I ended up using a global temporary table in Firebird, inserting parameter values first and to retrieve results I use a regular JOIN instead of a WHERE ... IN clause. The temporary table is transaction-specific and cleared on commit (ON COMMIT DELETE ROWS).

Parameterized SQL query in R with IN clause

To do this with string manipulation as in the question:

x <- "2000-01-01"
y <- "2001-01-01"
Item_Code <- c('A1','A2','B1','B2')

query <- sprintf("select * from Item_History
                  where Item_Exp_Date between '%s' and '%s'
                        and Item_Code in (%s)", x, y, toString(shQuote(Item_Code, 'sh')))

We could alternately use fn$ from the gsubfn package for string interpolation:

library(gsubfn)
query2 <- fn$identity("select * from Item_History
              where Item_Exp_Date between '$x' and '$y'
              and Item_Code in ( `toString(shQuote(Item_Code, 'sh'))` )")

How to parameterize IN clause in SQL Query to use in JMETER

First of all I would recommend reconsidering your whole approach because tests needs to be repeatable, if you need to check all the possible combinations of the emp and dept IDs - go for pairwise testing, store the generated queries in the CSV file and parameterize the queries using CSV Data Set Config.

If you still want to make the number of arguments absolutely random you can go for the following approach:

1. Add User Defined Variables to your Test Plan and define the following variables there:

   Emp_Id=3,9,11,12,13
   Dept_Name=HR,IT,Admin
   

2. Amend your query to include JMeter's __groovy() function like:

   Select * from Employee where Emp_Id in (${__groovy(def values = vars.get('Emp_Id').split('\,').collect(); values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)}) and Dept_Name in(${__groovy(def values = vars.get('Dept_Name').split('\,').collect{value -> "'" + value + "'"}; values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)})
   

Demo:

Can you only parameterize the where clause?

You are correct that you can't use parameters directly for this use case. However the fact if you need to use parameters or not depends on how sort gets populated.

If it is a list of column names that are hard coded and the user just picks which indexes in the list are going to be chosen you don't need to worry about parameterizing, the user does not have direct input in the query so they can't inject code in to it.

If the user is providing the column names directly you must clean up the user input before passing it in, a way to do that is use the sql function QUOTENAME to clean up the input.

string strQ = @"
declare @query nvarchar(max)
set @query = ';WITH lstTable as (
                SELECT ROW_NUMBER() OVER(ORDER BY' + QUOTENAME(@sortColumn) + ') AS RowNum, *
                FROM (
                SELECT *
                FROM SystemMessage
                WHERE Deleted = 0 '
exec sp_executesql @query";

What that will do is whatever string you pass in to @sortColumn it will properly wrap [ ] around that string value. It then uses that excaped value in the dynamicly generated string and runs that with sp_executesql.

One important note, this example only works with a single column name currently, you would need a QUOTENAME and a new parameter per column you wanted to add in to the query. If you did try passing in FirstName, LastName it would become

;WITH lstTable as (
   SELECT ROW_NUMBER() OVER(ORDER BY [FirstName, LastName]) AS RowNum, *
       FROM (
       SELECT *
       FROM SystemMessage
       WHERE Deleted = 0

when executed so it would attempt to find a column named "[FirstName, LastName]" and would fail.

Passing parameters to IN clause in SQL Server

Use any of the split functions from here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

---

Related Topics