Handling CSV in Stored Procedures
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
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)
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'
Comment on this tutorial
- Data Science
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
PreparedStatement Example in Java
Creating Database Tables Using ANT
Using the DriverManager Class vs Using a DataSource Object for a connection
Stored Procedures example in SQL
Result Sets, Cursors and Transactions in SQL