Introduction
Welcome to the fourth instalment of my series of articles on SQL.
Today I intend to address an issue that I often see asked about in
forums, that is, how to pass a list of values in to a stored proc and
use it to select or insert data. We will look at three ways of doing
this, and discuss the pros and cons of each.
Creating the tables
First, we’ll need some sample data. For this exercise, we’ll imagine
a table that stores names and birthdays and has an id for a key. Here
is the table SQL.
create table birthdays
(
id int identity,
firstName varchar(50),
lastName varchar(50),
birthday date
)
And here is the SQL to insert some values:
insert into birthdays values ('Fred', 'Smith', '1969/02/17'), ('Sarah', 'Smith', '1971/09/15'), ('Mary', 'Smith', '1996/07/20'),('Billy', 'Smith', '2000/07/01'), ('Sarah', 'Jones', '1945/12/26')
As you can see, just using SQL allows us to write one line to insert many values. More on that, later.
Now, the first thing we might want to do, is find people with the same surname or birthday, for example:
select * from birthdays where lastName = 'smith'
This is obviously easy to do with a parameter in a stored procedure.
But what if we had a reason to select a group of values by id, which
could be any length ? Here is the SQL:
select * from birthdays where id in (1,3)
But if you were to write a proc like this:
create proc findById1 @ids varchar(20)
as
begin
select * from birthdays where id in (@ids)
end
That would create fine. Now do this:
findById1 '1,3'
Your error is:
Msg 245, Level 16, State 1, Procedure findById1, Line 5
Conversion failed when converting the varchar value '1,3' to data type int.
SQL server cannot split your csv for you. It is literally doing this:
Select * from birthdays where id = ‘1,3’
Which, of course, is not what we want.
Passing in a CSL
The most obvious thing to do, is to pass the value in and parse it.
CREATE proc findById2 (@list nvarchar(MAX))
AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
select * from birthdays where id = convert(int, substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
END
And we can run it like this:
findById2 '1,3'
There’s a few issues here. Not least is that you get a table for each
row. But the code itself sure is ugly. Let’s get it all in to a temp
table and see how it looks:
CREATE proc findById3 (@list nvarchar(MAX))
AS
BEGIN
declare @list nvarchar(MAX) = '1,3'
create table #values
(
id int,
birthday date,
firstname nvarchar(50),
lastname nvarchar(50)
)
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
insert into #values
select id, birthday, firstname, lastname from birthdays where id = convert(int, substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
select * from #values
END
And you run it like this:
findById3 '1,3'
Try changing the values in the list and see what happens.
So, this works, but it’s clearly pretty ugly. We’re creating temp
tables, stepping over strings, doing all sorts of nasty stuff. This is
not something I recommend you’d ever do. I really included it to show
you how NOT to do things, and to show a BAD alternative you might
encounter ‘in the wild’. My first port of call in situations like this,
is to use XML.
XML processing
XML became a valid type for data in SQL Server 2005. Before that, a
function called OpenXML was used to parse XML. If you see code that
uses OpenXML and you’re not in SS2000, I recommend converting it.
http://www.sommarskog.se/arrays-in-sql-2005.html#XML is a great read on
the pros and cons of different ways to use XML. The short version is,
it’s better to store your values as attributes, than as text in nodes.
So, feel free to read that article, but, we’ll mostly be working with
XML that uses attributes.
The great advantage of using XML is, of course, that if your calls are
coming from procedural code ( and they almost certainly are ), odds are
you’re using a language that knows what XML is and offers all sorts of
tools for building it. Even DataTables in .NET can be serialised to
XML, as can any serializable class.
So, now we are going to create a proc which takes this as an input:
Here is the proc:
create proc findByIdWithXml @xml xml
as
begin
;with fromXml as
(
SELECT Y.ID.value('(@val)[1]', 'int') as id
FROM @xml.nodes('/ids/id') as Y(ID)
)
select b.id, firstName, lastName, birthday from birthdays b inner join fromXml x on x.id = b.id;
end
Call it like this:
findByIdWithXml '<ids><id val="1"><id val="3" /></id></ids>'
and it returns:
id firstName lastName birthday
1 Fred Smith 1969-02-17
3 Mary Smith 1996-07-20
Some things to note: we need to pass in the type of the object we want
to pull out, as a string. If we were to select /ids in the nodes
statement, we’d only get one node out, even though it has a many child
id nodes, we’d only select one. @ before the name, means it’s an
attribute. We use [1] because I’ve read that this makes the query
faster, although in the real world, it’s superfluous ( it means, grab
the first value in the group ).
This is definitely much neater than using a csl. But, it’s also more
powerful. Now we can define more than one way to get values out. Let’s
try this:
create proc findWithXml @xml xml
as
begin
;with fromXML as
(
SELECT
Y.bday.value('(@id)[1]', 'int') as id,
Y.bday.value('(@fname)[1]', 'varchar(255)') as fname
FROM @xml.nodes('/birthdays/bday') as Y(bday)
)
select b.id, firstName, lastName, birthday from birthdays b inner join fromXml x on x.id = b.id or x.fname = b.firstName;
end
Now we’re allowing two different types of search, id and first name.
Obviously, we could expand this as much as we wanted. The CTE returns
this:
id fname
1 NULL
NULL Sarah
4 NULL
But this is fine, because NULL does not equal NULL in SQL. So even if
there’s nulls in the source table ( which I allowed ), it would still
not match on them.
findWithXml '<birthdays><bday id="1"><bday fname="Sarah"><bday id="4" /></birthdays>'
returns this:
id firstName lastName birthday
1 Fred Smith 1969-02-17
2 Sarah Smith 1971-09-15
5 Sarah Jones 1945-12-26
4 Billy Smith 2000-07-01
Note that my use of the name ‘Sarah’ has confirmed that this will return
multiple matches. Note also that I’ve not used ‘ORDER BY’ in my proc,
so they are not in id order. Of course, this is easily resolved.
INSERTS with XML
Of course, now that we’ve moved to a strongly defined format, we can
start to also use XML to do inserts ( of course, we could always do
deletes, because we’d delete on only one value, same as we select ).
The concept is the same across the board, use a CTE to create an in
table memory, and then use it with our existing tables.
create proc insertWithXml @xml xml
as
begin
;with fromXML as
(
SELECT
Y.bday.value('(@fname)[1]', 'varchar(255)') as fname,
Y.bday.value('(@lname)[1]', 'varchar(255)') as lname,
Y.bday.value('(@date)[1]', 'date') as bday
FROM @xml.nodes('/birthdays/bday') as Y(bday)
)
insert into birthdays (firstname, lastname, birthday)
select fname, lname, bday from fromXml;
end
Note that the column names don’t have to be the same, just in the same order.
insertWithXml '<birthdays><bday fname="Hannah" lname="Jones" date="1972/12/31"><bday fname="Calvin" lname="Kaputnik" date="1914/11/11" /></birthdays>'
Now select all and see the result:
select * from birthdays
Two values have been inserted. Note that this is not as efficient as
using a proper bulk insert process if you have TONS of records, but it’s
great if you need to insert a small number at a time and want to call a
proc once, instead of X times.
Table Value Parameters
Starting in SS2008, it is possible to pass in a table as a parameter.
While our code above was quite compact, there was a separate step to
turn XML in to a table, and although I’ve never had any issue with
doing this, I am told there are quirks to watch out for. So, this seems
better right ?
There are a few gotchas to be aware of. First of all, TVPs inside a
proc must be marked as read only ( which means you also can’t insert in
to them or delete from them ). That seems reasonable. Secondly, TVPs
require you to define them first. This means that if you have a proc
that takes a TVP, you need to also install the TVP on your SQL Server
instance for it to work, and you can easily end up with a DB full of
TVPs, which strikes me as more confusing than just using one type that
is natively supported.
But the real issue that I can see is that, as far as I can tell, TVPs
are not supported by entity framework. This is a real blow, if you
could create a List of a class and pass it to a proc, that would be very
powerful indeed. As it stands, it means that even if you don’t use EF,
using TVPs closes the door on using EF for any other projects you need
to write against your DB. I know I’ve often had to write small, single
use apps, and having EF create my data layer for free has always been a
very powerful reason to use it.
With those caveats in mind, let’s press on.
CREATE TYPE BirthdayTableType AS TABLE
(
id int,
firstName varchar(50),
lastName varchar(50),
birthday date
);
GO
This creates the actual TVP type. As you can see, it’s a table
declaration, with some small syntax changes. In fact, I copied and
pasted the code for our table to create this, and just removed the
identity specification ( it turns out this is valid, although it’s
clearly largely useless ).
And here is the proc:
CREATE PROCEDURE dbo.findWithTVP
@TVP BirthdayTableType READONLY
AS
begin
select * from birthdays b
inner join @TVP t on t.id = b.id or t.birthday = b.birthday or t.firstname = b.firstname or t.lastname = b.lastname
end
This is certainly even neater than the XML solution, and it’s a world
away from the comma seperated list solution, both in usefulness and in
readability.
While we’re clearly not typically going to create TVPs in SQL, this bit
is actually more painful.
DECLARE @tvp AS BirthdayTableType;
INSERT INTO @tvp (id, firstname, lastname, birthday) values(1, null, null, null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, null, 'kaputnik', null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, 'sarah', null, null)
INSERT INTO @tvp (id, firstname, lastname, birthday) values(null, null, null, '1996-07-20')
exec findWithTVP @tvp
And this will return the records we would expect. And, again, we can
easily write insert code using a TVP, and, I believe that it’s possible
in normal ( non-EF ) .NET code, to just pass a datatable through as a
parameter. So, this would make for neat code with a traditional data
layer, where the result of one proc needs to be passed to another proc.
Note