There is any chance to pass arrays to SQL Functions from .NET C#.
Thanks in advance.
Sincerely.
And Could you show me how ?
Add a comment
|
2 Answers
With SQL Server 2008, yes, using table-valued parameters. With SQL Server 2005, you'd have to wrap the data in XML and decompose it using XQuery in your T-SQL function.
NB: you can only pass table-valued parameters to stored procs--not to functions.
From the link:
// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand(
"usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam =
insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;
Comments
Erland Sommarskog has two excellent articles on how to do this in SQL Server 2000 and 2005:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html
But with SQL Server 2008 and table-valued parameters, it's much nicer and easier, so if you're on 2008, definitely use TVP !
Marc
