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:
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,AdminAmend 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
Update Columns in the Same Table With Different Values With Postgres
How to Get the Unmatched Records from Two Tables Using Joins
Sql Query for Increase Item Value Price for Multiple Item
How to Enforce Case Sensitive Table and Column Names in MySQL
Comma Separated Values in SQL Server Returning Duplicates
How to Calculate Date Difference in Postgresql
Get Only Records Created Today in Laravel
Mysql Workbench Edit Table Data Is Read Only
Ssis Date in Derived Column as Yyyy-Mm-Dd Format
How to Subtract One Day from Current Date Then Convert to String in Hive
Spring Data JPA Utf-8 Encoding Not Working
Pg_Restore: [Archiver] Unsupported Version (1.14) in File Header
Ssis Failed Validation and Returned Validation Status "Vs_Isbroken"
How to Combine Two Completely Different SQL Queries into One Result
How to Use SQL Like Condition With Multiple Values in Postgresql
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different