开发者

SQL Server 2008 Receive an user-defined table type in a CLR Stored Procedure?

I have table-valued parameters in SQL Server 2008, e.g.:

CREATE TYPE UserType AS TABLE
(
    UserI开发者_运维百科D int,
    UserName nvarchar(100),
    UserPassword nvarchar(100)
)

Can I use this type somehow in my SQL CLR stored procedure? For example as input parameter?

[SqlProcedure]
public static void SomeFunction (/* what type should be here ?? */)
{
     //
}


I don't believe you can. The page Mapping CLR Parameter Data indicates that there are no (SQL Server CLR or .NET Framework) equivalents for the table data type.


Or, to quote from @Jani's link:

A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process


Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned.

In contrast, CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

Reference


Try use class instead of table-valued parameter:

public class UserType
{
    int UserID { get; set; }
    string UserName { get; set; }
    string UserPassword { get; set; }
)

[SqlProcedure]
public static void SomeFunction (UserType type)
{
     //
}
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜