dvplayltd
asked on
Advanced passed of params with SQL 2008
Dear experts
I need to create a stored procedure which saves more than 1 records /actually the records can be 1 or 200/. It will be faster not to call this SP 200 times, but to pass all these 200 params (bigint) to stored procedure. How can I do this? In C# a similar case is solved with pass a string value for example 3454,4456,456546,1265 then to split the string and to loop. Is such approach possible with SP for MS SQL Server 2008 ?
I need to create a stored procedure which saves more than 1 records /actually the records can be 1 or 200/. It will be faster not to call this SP 200 times, but to pass all these 200 params (bigint) to stored procedure. How can I do this? In C# a similar case is solved with pass a string value for example 3454,4456,456546,1265 then to split the string and to loop. Is such approach possible with SP for MS SQL Server 2008 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
10x. Perfect solution, i'll use it!
Microsoft has introduced user defined Table Type Variable in sql server 2008. You can defined a table type variable in your database and can use the same at ado.net side.
Actually through this mechanism you can pass whole table as a parameter to your stored procedure and can use it as a table .
for more detail search User Defined Table Types and Table-Valued Parameters
or look into below link..
http://www.codeproject.com