sql-server-2008 - 存储过程中可为空的表值参数

Question

我有这个程序

CREATE PROCEDURE dbo.spProcedure1
    @intArray as dbo.intArray READONLY
AS
BEGIN
-- ...
END

使用用户类型作为参数

CREATE TYPE dbo.IntArray AS TABLE (IntValue int NULL)

我正在从 C# ASP.NET MVC 4 项目中调用过程

    // creating empty SQL @IntArray parameter

        var emptyIntDataTable = new DataTable();
        emptyIntDataTable.Columns.Add("IntValue");

    // calling stored procedure

        return Database.SqlQuery<int>(
            @"spProcedure1 @p1",
            new SqlParameter("p1", (object)Utils.ToDataTable(m.IntArray) ?? emptyIntDataTable)
        ).ToList();

    // ToDataTable method which is returning null

        public static DataTable ToDataTable<T>(this IList<T> data)
                {
                    if (data == null)
                        return null;

                    ... // code omitted because it is not working yet                
}

调用存储过程抛出的错误是

The table type parameter 'p1' must have a valid type name.

如何传递空表值？

传递列表而不是数据表抛出以下错误

var emptyIntDataTable = new List<int>;

No mapping exists from object type System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

Answer 1

在您的代码中:

它说:

return Database.SqlQuery<int>(
    @"spProcedure1 @p1", new SqlParameter("p1", 
        (object)Utils.ToDataTable(m.IntArray) ?? emptyIntDataTable)
).ToList();

改成:

 return m.IntArray.Length > 0? 
        Database.SqlQuery<int>(@"spProcedure1 @p1",
            new SqlParameter("p1", 
               (object)Utils.ToDataTable(m.IntArray))).ToList():
        Database.SqlQuery<int>(@"spProcedure1")).ToList();

展示如何不传递表参数的示例

CREATE TYPE dbo.KeyIds]
AS TABLE(pkId int NOT NULL,
PRIMARY KEY CLUSTERED (pkId ASC)
WITH (IGNORE_DUP_KEY = OFF))
Go
-- ------------------------------
Create procedure testProc 
    @aIds dbo.keyIds readonly
as 
Set NoCount On
    if exists (select * from @aIds) 
        Select * from @aIds
    else
        Select 'No Aids passed in'
Go
-- ------------------------------

Exec dbo.testProc -- <--- Here I am NOT passing the @aids parameter

但是，即使我没有传递@aids 参数 它仍然有效，子查询 (select * from @aIds) 仍然有效，并且由于它是一个空数据表，SP 返回空消息“No Aids passed in”。

另一方面，如果你传递参数

Declare @MyIds dbo.keyIds
Insert @MyIds Values(1)
Insert @MyIds Values(2)
Insert @MyIds Values(3)
Insert @MyIds Values(4)
Insert @MyIds Values(5)
Exec dbo.testProc @MyIds -- <--- Here I AM passing the @aids parameter

输出datatable参数的内容

C# 代码示例...

 public DataTable GetAccountTransactions(IEnumerable<int> accountIds)
    {
        const string procName = "FetchAccountTransactionData";

        var acctIds = accountIds == null ? 
              new List<int>() : accountIds.ToList();
        // -------------------------------------------------
        var parms = DbParamList.Make(); 
            // DbParamList is a List<IDbDataParameter>
        // See here, ONLY ADD PARAMETER if list is NOT empty!
        if (acctIds.Count > 0) 
            parms.AddSQLTableParm("aIds", acctIds);

        try
        { // following constructs command obkect and calls SP
            return Utilities.GetDataTable(schemaNm + "." + 
                            procName, parms, copConn);
        }
        catch (SqlException dbX)
        { 
           // Exception stuff
        }
    }


    public class DbParamSortedList : SortedList<string,IDbDataParameter> { }