Parameters are a great feature in any SQL implementation. You can protect yourself – and more likely – your clients from SQL injection by always using parameterised queries for every call to the database.
I’ve seen some implementations in e-commerce packages where SQL injection would be a breeze.
The Scenario
For a recent client I had a .Net app which was to query records but exclude a set from a specific set of criteria. Normally this is either done with a join or an IN clause. In this instance as I am passing the criteria by parameter I needed a IN Clause.
A IN clause is used to either include or exclude records from a result. An example would be:
Select * from TableA where TableA.name IN ('John','Frank','Dave')
This would include only those rows where column name matched one of ‘John’ or ‘Frank’ or ‘Dave’.
The Problem
Now there is no current way to send a array of items through a parameter. So your choices are:
- Invoke the query once for each criteria item, or
- Use dynamic SQL
Both of these are highly inefficient, to say the least.
The Solution
Now before you start pulling your hair out, there is a simple and elegant solution. In T-SQL you can use XML, yes I did write XML that is not a typo.
Here’s how
In T-SQL you can have script which looks like:
Declare @x xml
@x = '<L>
<I>John</I>
<I>Frank</I>
<I>Dave</I>
</L>'
Select * from TableA where TableA.name in (
Select * from ( select T.c.value('.','uniqueidentifier') as source
from @x.nodes ('/L/I') T(c) ) Idss )
The inner query converts the XML into a result set by breaking out the individual <I> nodes within the <L> node into logical rows. The ‘T.c.value’ then accesses the logical row and extracts the text within. Finally this is presented as a subquery result named Idss (apparently that part is important otherwise you’ll get an error).
Now all you would need to do is to place your XML in the @x parameter. I chose a parameter type of string as that was simpler to handle.
Conclusion
The XML solution is quite elegant requiring minimal effort on the client side to pass the parameters to the server. This is much faster than dynamic SQL, as the server store the plan away and will save you recompiles on the server – always a performance killer.
You’ve seen how simple it is to specific a list of values for an IN clause as a parameter. A lot simpler than either of thought!