How to pass a collection of data to SQL Server

Hi all. If you are faced with a situation where you wish to send an array of strings(say) to the Server, then you have stumbled to the correct page :-)
To start off with, SQL server up to 2005 does not support Arrays. So, it becomes a bit difficult for Java,C#,C++ programmers who are very fond of arrays to get their job done.

Fortunately there is a detour which enables us to get the job done.
Instead of sending the list as an array, we can append a delimiter(here I used the comma) and send the complete string to the server side.
At the server side, it splits the separate strings by the delimiter and inserts them into a table(her I used it as a single column table of the form
tmpTable(_name varchar(50)))
You can use the values inserted into this table and use the results similar to having an array.

NOTE: It does not support the sophisticated array functions you are used to, but it will let you get the job done.

/*
PROCEDURE NAME : readCSV
AUTHOR : Ruchira Randana
PROCEDURE DESCRIPTION : Used to seperate a Comma Seperated List and input the values to a table
INPUT : Comma Seperated string
OUTPUT : Seperated values as the comma for the delimitter
*/
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

create procedure readCSV

@CSValueString varchar(2500)

as begin

declare @index int
declare @length int
declare @nextCommaIndex int
declare @value varchar(50)

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

set @length=len(@CSValueString)
set @nextCommaIndex=charindex(',',@CSValueString,1)

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

while((@nextCommaIndex>0) or (len(@CSValueString)>0))
begin

if(len(@CSValueString)>0 and @nextCommaIndex=0)
begin
set @value=ltrim(rtrim(@CSValueString))
set @nextCommaIndex=-1
set @CSValueString=''
end
else
begin
set @value=substring(@CSValueString,1,@nextCommaIndex-1)
set @value=ltrim(rtrim(@value))
set @CSValueString=substring(@CSValueString,@nextCommaIndex+1,len(@CSValueString))
set @nextCommaIndex=charindex(',',@CSValueString,1)
end

insert into tmpTable values(@value)
end

end

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--exec readCSV 'Ruchira,randana,galappaththi,ruch'
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

To run this code, first create a table named 'tmpTable' schema(_name varchar(50)) on your Data Base. This can be done by the following.

create table tmpTable(_name varchar(50))

After creating the table, run the above code on your database. To test the procedure, uncomment the bottom line 'exec readCSV 'Ruchira,randana,galappaththi,ruch'' and execute.

With a little bit of effort, you can get this up and running.

Comments

Post a Comment

Popular posts from this blog

Encrypt and Decrypt Images Using Java

Hi, Most of you would find it useful to Encrypt a special picture of yours such that only you can view it. By using JCE(Java Cryptography Extension) classes, this becomes very simple. As I have already done before using Java to encrypt a String is very simple. If you are new to this, please read the article which I had already posted named "Java Encryption and Decryption" at "http://ruchiram4.blogspot.com/2009/04/java-encryption-and-decryption.html" Also, you need to have a basic understanding of Java IO. If you are new, it is better to read the Sun Java tutorial on IO. Before we move on, lets recall the basic steps in Encrypting plaintext. 1. Creating an instance of "Cipher" object and setting the mode of Encryption (Eg: AES, DES) 2. Generating a key by using a KeyGenerator object. 3. Setting the mode of Cipher operation on the Cipher object. As we intent to Encrypt, we simply set it by setting a property of Cipher object by calling its "init"...
Read more

kSoap2 printing request dump

Hi, kSoap2 is a library for SOAP which is used in Androids. Here's a neat little trick, if you want to see the actual XML sent received. 1. Set httpTransport.debug=true; before making the call 2. Read values from the httpTransport object afterwards String s1 = httpTransport.requestDump; String s2 = httpTransport.responseDump; Have fun !!!
Read more

ASP Response.Write newline

Hi, all of you who have used ASP may have encountered situations where you require to write a text output to the screen. It can be easily done by using Response.Write("Hello world"); However, if you wish to append some more text to a newline, it would not work in the following ways. Response.Write("Hello world \n"); Response.Write("Good day."); The output of the above lines would be Hello world Good day. and NOT Hello world Good day. In order to overcome this, you can very simple place an html break tag inside the code. Response.Write("Hello world"); Response.Write("<br/>"); Response.Write("Good day."); The output would be the following. Hello world Good day. Cheers !!!
Read more