Passing Table to SP

I have a table of about 20-200 rows that I would like to pass to a stored
procedure as a parameter. Don't need any results back. the table will be in
a known format to the sproc. This is not really an insert or update function
as the sproc will process these values, but the data does not get stored as
is in SQL. The sproc may perform a series of other updates as a result of
these parameter / values.

Basically a table as a parameter.

Kind of the opposite of what I normally do with a table or dataset.

Maybe I am making more of this that need be or missing something obvious?
Sorry for the vague question.

-Andrew

Andrew
Thu Feb 17 18:56:58 CST 2005

basically, I am trying to pass a number of values to SQL in a single round
trip. I could Call the same sproc n number of times, but don't like to
concatinate calls because of things like sql injection. I could do something
like:

EXEC Proceedure1 @ID=123, @Company='ABC';
EXEC Proceedure1 @ID=456, @Company='DEF';
.
.
.
EXEC Proceedure1 @ID=789, @Company='XYZ';

thanks,


"Andrew Robinson" <nemoby@nospam.nospam> wrote in message
news:euHirKVFFHA.1296@TK2MSFTNGP10.phx.gbl...
> I have a table of about 20-200 rows that I would like to pass to a stored
> procedure as a parameter. Don't need any results back. the table will be
in
> a known format to the sproc. This is not really an insert or update
function
> as the sproc will process these values, but the data does not get stored
as
> is in SQL. The sproc may perform a series of other updates as a result of
> these parameter / values.
>
> Basically a table as a parameter.
>
> Kind of the opposite of what I normally do with a table or dataset.
>
> Maybe I am making more of this that need be or missing something obvious?
> Sorry for the vague question.
>
> -Andrew
>
>

v-kevy
Thu Feb 17 20:06:35 CST 2005

Hi Andrew,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need pass a whole DataTable to the
stored procedure as parameter. If there is any misunderstanding, please
feel free to let me know.

As far as I know, we cannot pass a whole table to the stored procedure, as
SQL doesn't accept such type. So what I suppose, is to call the same sproc
n number of times as you mentioned. This is the workaround for the
limitation.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Andrew
Thu Feb 17 21:12:13 CST 2005

Kevin,

Yes, I think you have a good handle on what I want to do. Didn't think there
was any way to pass a table. I guess I could pass XML with Yukon, but that
will have to wait. So, yes, I can make multiple calls to the same sproc and
even do that as a single round trip to SQL by using a single call.

1. Is there a way that I can use Parameter.Add to build each 'sub-call' and
then concatenate the resulting string before passing the whole thing off to
SqlCommand? Mainly interested in avoiding sql injection type issues. I have
always coded with parameters and would like to continue using them.

2. If each of the sprocs returns a single scalar value, (in this case a
guid), is it reasonable to use my concatenated sql query with a DataSet.Fill
method and in effect return 'n' number of tables. Then iterate through the
table collection on the dataset, each table having a single row and column.
If I passed 200-300 subqueries to sql and wound up with 200-300 tables in
the DataSet, am I pushing the limits? Is there an upper limit? Is this a
reasonable approach?


thanks,

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:ZCdoL7VFFHA.3612@TK2MSFTNGXA01.phx.gbl...
> Hi Andrew,
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you need pass a whole DataTable to the
> stored procedure as parameter. If there is any misunderstanding, please
> feel free to let me know.
>
> As far as I know, we cannot pass a whole table to the stored procedure, as
> SQL doesn't accept such type. So what I suppose, is to call the same sproc
> n number of times as you mentioned. This is the workaround for the
> limitation.
>
> HTH.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

Andrew
Fri Feb 18 11:52:46 CST 2005

To answer my own question, I did a bit of instrumentation on this.

1. SELECT NewID(); SELECT NewID(); SELECT NewID(); ..... 1000 times and fill
it into a single DataSet using a DataAdaptor.

or

2. SELECT NewID(); and loop through 1000 times with a
Command.ExecuteScalar() and leaving the connection open.

the latter was about 3 to 4 times faster.

-Andrew


"Andrew" <nemoby@nospam.nospam> wrote in message
news:u6IRoeWFFHA.3908@TK2MSFTNGP12.phx.gbl...
> Kevin,
>
> Yes, I think you have a good handle on what I want to do. Didn't think
> there was any way to pass a table. I guess I could pass XML with Yukon,
> but that will have to wait. So, yes, I can make multiple calls to the same
> sproc and even do that as a single round trip to SQL by using a single
> call.
>
> 1. Is there a way that I can use Parameter.Add to build each 'sub-call'
> and then concatenate the resulting string before passing the whole thing
> off to SqlCommand? Mainly interested in avoiding sql injection type
> issues. I have always coded with parameters and would like to continue
> using them.
>
> 2. If each of the sprocs returns a single scalar value, (in this case a
> guid), is it reasonable to use my concatenated sql query with a
> DataSet.Fill method and in effect return 'n' number of tables. Then
> iterate through the table collection on the dataset, each table having a
> single row and column. If I passed 200-300 subqueries to sql and wound up
> with 200-300 tables in the DataSet, am I pushing the limits? Is there an
> upper limit? Is this a reasonable approach?
>
>
> thanks,
>
> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> news:ZCdoL7VFFHA.3612@TK2MSFTNGXA01.phx.gbl...
>> Hi Andrew,
>>
>> First of all, I would like to confirm my understanding of your issue.
>> From
>> your description, I understand that you need pass a whole DataTable to
>> the
>> stored procedure as parameter. If there is any misunderstanding, please
>> feel free to let me know.
>>
>> As far as I know, we cannot pass a whole table to the stored procedure,
>> as
>> SQL doesn't accept such type. So what I suppose, is to call the same
>> sproc
>> n number of times as you mentioned. This is the workaround for the
>> limitation.
>>
>> HTH.
>>
>> Kevin Yu
>> =======
>> "This posting is provided "AS IS" with no warranties, and confers no
>> rights."
>>
>
>

v-kevy
Fri Feb 18 23:51:28 CST 2005

Hi Andrew,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

William
Sun Feb 20 15:45:56 CST 2005

Export the table to SQL Server via BCP/DTS. Once the temp table is on the
server, you can include it in the logic in the SP server-side--this will be
dramatically faster.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Andrew Robinson" <nemoby@nospam.nospam> wrote in message
news:euHirKVFFHA.1296@TK2MSFTNGP10.phx.gbl...
>I have a table of about 20-200 rows that I would like to pass to a stored
> procedure as a parameter. Don't need any results back. the table will be
> in
> a known format to the sproc. This is not really an insert or update
> function
> as the sproc will process these values, but the data does not get stored
> as
> is in SQL. The sproc may perform a series of other updates as a result of
> these parameter / values.
>
> Basically a table as a parameter.
>
> Kind of the opposite of what I normally do with a table or dataset.
>
> Maybe I am making more of this that need be or missing something obvious?
> Sorry for the vague question.
>
> -Andrew
>
>