July 20, 2017 at 7:49 am
How to pass a dataset as parameter to a stored procedure?
July 20, 2017 at 7:53 am
adonetok - Thursday, July 20, 2017 7:49 AMHow to pass a dataset as parameter to a stored procedure?
Depends, what is the structure of the data set?
😎
July 20, 2017 at 7:58 am
For demo, like script which generated from app.
select * from myorder where city='London'
July 20, 2017 at 8:13 am
adonetok - Thursday, July 20, 2017 7:58 AMFor demo, like script which generated from app.
select * from myorder where city='London'
You will have to be much more specific as this is far to ambiguous, dose that table have one or one hundred columns, what are the data types, cardinality etc.
😎
You have been around long enough to no that we need a lot more inforamation😉
July 20, 2017 at 8:20 am
Table-type parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2017 at 9:01 am
In your database, create a user-defined table type that matches the columns in "myorder". Your stored procedure can then specify a parameter that uses this table type instead of one of the built-in types. Inside the stored procedure you can then use the parameter in much the same way as a table. In your application, declare the parameter as SqlType.Structured (that's C#, I assume other languages will have similar constructs available) and assign the parameter value to be the dataset.
July 21, 2017 at 4:58 pm
Convert your dataset to xml and pass xml string to stored procedure.
In procedure you can extract the xml string into a temp table and do the rest.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply