Tuesday, July 18, 2006

Sending a DataTable as parameter to Stored Procedure(SQL Sever 2000)

Quite often i faced a cese where i need to send a DataTable or array to the procedure as parameter. As we all know SQL 2000 takes paramteres as varchar,char,int,text etc. But there are occation where we required to send Full DataTable to DB. In those case we usually pump data to DB, record by record. Here is a work around for that. Lets Assume we have to send a table(see below) as parameter to Stored Procedure.

Person Discovery
EinsteinTheory of relativity
NewtonLaws of Cooling
Niels BhoreAtomic Model
Now convert the above table as string using a (C#)class.The resultant string looks like this
"
'''Person'',''Discovery'',~''Einstein'',''Theory of relativity'',~''Newton'',''Laws of Cooling'',~',~''Niels Bhore'',''Atomic Model'',~'
"
We can send this string to SP as a parameter directly. So the SP that take this string as input parameter looks as below.
Create Procedure usp_update_peopleTable
@inputTable varchar(8000)
----
as
begin
---
end

But there is constraint as varchar can just hold(8000 chars) so we have to declare a variable type which can hold more data. So we have to use Text/Image instead of Varchar. so the SP looks as below.
Create Procedure usp_update_peopleTable
@inputTable text
----
as
begin
---
end
I just want to repat the the stuff what we done still now.In App server we have a table that we need to send to DB. we converted the table as a string, now we sent the string to SP.
Now we have to chage input string to SP as Table again so that we can manipulate that as we require.So next task is to covert the String to Table in DB. Below SP address the same task. This SP takes 2 input parameters and results a Table, 1st param the strig that u have to convert to a table, 2nd param number of columns of the table. Teh SP is as below.

alter procedure usp_get_table_from_text
@inputtxt text,
@no_cols int
as
begin
declare @charposition int,@row varchar(8000),@strsql varchar(8000)
create table #data_holder
(
rowid int,
textvalue text
)

set @strsql = 'create table output_table ('
while(@no_cols != 0)
begin
set @strsql = @strsql+ ' col' + cast(@no_cols as varchar) + ' varchar(2000),'
set @no_cols = @no_cols -1
end
set @strsql = substring(@strsql,1,len(@strsql)-1)+ ')'

exec(@strsql)

set @charposition = charindex('~',@inputtxt)
insert into #data_holder values(1,@inputtxt)

while(@charposition > 1)
begin
select @row = substring(textvalue,1,@charposition-1) from #data_holder where rowid = 1

set @strsql = 'insert into output_table values(' + substring(@row,1,len(@row)-1) + ')'
exec(@strsql)

update #data_holder set textvalue= substring(textvalue,@charposition+1,datalength(textvalue))


select @charposition = charindex('~',textvalue) from #data_holder where rowid = 1

end
drop table #data_holder
select * from output_table
drop table output_table
end

Just for understanding create SP in u r DB and execute below Query. SO that you can get some understanding what its doing.

exec usp_update_peopleTable '''Person'',''Discovery'',~''Einstein'',''Theory of relativity'',~''Newton'',''Laws of Cooling'',~',~''Niels Bhore'',''Atomic Model'',~',2

I am so sorry to say that there were no cooments in above SP. i'll add comments as and when i get soem time.
Conclusion:
Steps to use the stuff
Objective: when vere u need to send a Table directly to DB folow these steps.
1. Covert the Table to String in App Server.
2. send teh converted string to your SP that consumes the Table.
3. In your SP use sp 'usp_get_table_from_text' to convert the string to table
4. Manipulate the table as you want.
Hope this might help you all.

14 comments:

Deeps said...

Was curious to know which C# class was used to convert table to string. I have one such requirement and would like to know the same.

Anonymous said...

Hi, you can find this info by using search box in the top of website.

Best regards

http://qualitymanagement.hrvinet.com/sa-8000-procedures/

Anonymous said...

Howdy,

When ever I surf on web I come to this website[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url]Plenty of useful information on ssurendra.blogspot.com. I am sure due to busy scedules we really do not get time to care about our health. Are you really serious about your weight?. Recent Research presents that closely 60% of all United States grownups are either chubby or overweight[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url] So if you're one of these citizens, you're not alone. Its true that we all can't be like Brad Pitt, Angelina Jolie, Megan Fox, and have sexy and perfect six pack abs. Now the question is how you are planning to have quick weight loss? You can easily lose with with little effort. You need to improve some of you daily habbits to achive weight loss in short span of time.

About me: I am blogger of [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss tips[/url]. I am also health trainer who can help you lose weight quickly. If you do not want to go under hard training program than you may also try [url=http://www.weightrapidloss.com/acai-berry-for-quick-weight-loss]Acai Berry[/url] or [url=http://www.weightrapidloss.com/colon-cleanse-for-weight-loss]Colon Cleansing[/url] for effortless weight loss.

Anonymous said...

I think, that you are not right. I am assured. Let's discuss it. Write to me in PM.

Anonymous said...

I apologise, I can help nothing, but it is assured, that to you will help to find the correct decision.

Anonymous said...

It's awesome to pay a quick visit this site and reading the views of all mates about this piece of writing, while I am also zealous of getting know-how.

My webpage ::
Also see my web page >

Ganga Raju said...

Thanks surendra.. helped a lot.

Anonymous said...

My brother recommended I might like this blog.

He was totally right. This post actually made my day. You can
not imagine simply how much time I had spent for this information!
Thanks!

Take a look at my web-site; xengthreview.net

Anonymous said...

Really when someone doesn't be aware of afterward its up to other viewers that they will help, so here it occurs.

Look into my web site ... Natural Weight Loss

Anonymous said...

Attractive section of content. I just stumbled upon your site and in accession capital to assert
that I acquire actually enjoyed account your blog posts.
Anyway I will be subscribing to your augment and even I achievement
you access consistently rapidly.

my web site: Green coffee reviews

Anonymous said...

What's up mates, how is everything, and what you desire to say regarding this article, in my view its in fact awesome designed for me.

Have a look at my page - Pur Essence Anti Aging

Anonymous said...

Wow, that's what I was exploring for, what a data! present here at this website, thanks admin of this site.

Here is my web blog; real online jobs

Anonymous said...

Please let me know if you're looking for a author for your blog. You have some really good articles and I believe I would be a good asset. If you ever want to take some of the load off, I'd love
to write some material for your blog in exchange for a link
back to mine. Please blast me an e-mail if interested.
Many thanks!

Feel free to surf to my weblog ... Order Natural Cleanse

Anonymous said...

Hi, i think that i saw you visited my web site thus i came to go back the favor?
.I am trying to to find things to improve my web site!I assume its adequate to use a few of your
ideas!!


turbo force muscle