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 |
| Einstein | Theory of relativity |
| Newton | Laws of Cooling |
| Niels Bhore | Atomic 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.
