Handling CSV in Stored Procedures

By: Balmer Emailed: 1587 times Printed: 2037 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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'

JDBC Home | All JDBC Tutorials | Latest JDBC Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(0)


Be the first one to add a comment

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Balmer
Handling CSV in Stored Procedures

More Tutorials in JDBC
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
Import TEXT to TABLE in MSSQL
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
setSavepoint and releaseSavepoint Example in Java
Calling a Stored Procedure from JDBC in Java
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
JDBC Basics and JDBC Components
SELECT Statements
WHERE Clauses in SQL
Joins example in SQL
Common SQL Commands
Result Sets, Cursors and Transactions in SQL
Stored Procedures example in SQL

More Latest News
Most Viewed Articles (in JDBC )
A simple JDBC application sample code
How connection pooling works in Java and JDBC
JDBC Components
Using JDBC to extract data from a database and output to an XML document
Using the DriverManager Class vs Using a DataSource Object for a connection
PreparedStatement Example in Java
The Structure of JDBC
WHERE Clauses in SQL
JDBC Architecture
What is the ACID principal?
Using JDBC to connect to MySQL from Java Program
JDBC Basics and JDBC Components
Calling a Stored Procedure from JDBC in Java
Data Access Technologies in Java
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
Most Emailed Articles (in JDBC)
The Structure of JDBC
A simple JDBC application sample code
JDBC Components
What is the ACID principal?
Using Transactions in JDBC
How connection pooling works in Java and JDBC
Using JDBC to extract data from a database and output to an XML document
Using JDBC to connect to MySQL from Java Program
JDBC and Tomcat context settings
PreparedStatement Example in Java
Result Sets, Cursors and Transactions in SQL
Data Access Technologies in Java
WHERE Clauses in SQL
Joins example in SQL
Common SQL Commands