Programming Tutorials

(追記) (追記ここまで)

Handling CSV in Stored Procedures

By: Balmer in JDBC Tutorials on 2008年08月05日 [フレーム]

You have a number of key values, identifying a couple of rows in a table, and you want to retrieve these rows. If you are the sort of person who composes your SQL statements in client code, you might have something that looks like this:

SQL = "SELECT ProductID, ProductName FROM Northwind..Products " & _
"WHERE ProductID IN (" & List & ")"
rs = cmd.Execute(SQL)

List is here a variable which you somewhere have assigned a string value of a comma-separated list, for instance "9, 12, 27, 39".

This sort of code above is bad practice, because you should never interpolate parameter values into your query string. (Why is beyond the scope of this article)

Since this is bad practice, you want to use stored procedures. However, at first glance you don't seem to find that any apparent way of doing this. Many have tried with:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind..Products
WHERE ProductID IN (@ids)
But when they test this:

EXEC get_product_names '9, 12, 27, 37'
The reward is this error message:

Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2
Syntax error converting the varchar value '9, 12, 27, 37' to a column
of data type int.
This fails, because we are no longer composing an SQL statement dynamically, and @ids is just one value in the IN clause. An IN clause could also read:

... WHERE col IN (@a, @b, @c)
Or more directly, consider this little script:

CREATE TABLE #csv (a varchar(20) NOT NULL)
go
INSERT #csv (a) VALUES ('9, 12, 27, 37')
INSERT #csv (a) VALUES ('something else')
SELECT a FROM #csv WHERE a IN ('9, 12, 27, 37')
The correct way of handling the situation is to use a function that unpacks the string into a table. Here is a very simple such function:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
 RETURNS @tbl TABLE (number int NOT NULL) 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
 INSERT @tbl (number)
 VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
 SELECT @pos = @nextpos
 END
 RETURN
END

The function simply iterates over the string looking for commas, and extracts the values one by one. The only complexity is the logic to handle the last value in the string. Here is an example of how you could use this function:

CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
 SELECT P.ProductName, P.ProductID
 FROM Northwind..Products P
 JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
go
EXEC get_product_names_iter '9, 12, 27, 37'

Previous
<< java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError

Next
What is Referential Integrity in databases?>>

(追記) (追記ここまで)