Passing Table to SP

[Andrew Robinson]

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 Robinson]

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,

 

[Kevin Yu [MSFT]]

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]

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,

 

[Andrew]

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

 

[Kevin Yu [MSFT]]

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 \(Bill\) Vaughn]

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.
__________________________________