Question

我有一张表格 DEPT ,内有两列 - ID, NAME 。

搜索表格与 DEPT 表格中的ID一起显示,用户可以选择任何数量的ID并提交表格,以获得相关的NAMEs 。

澄清/投入:

I don t want to build a dynamic query - its not manageable.
I prefer a stored procedure using table-valued parameters

还有其他解决办法吗?

NOTE:
This example is simple with 1 table - in real life, I have to deal with more than 6 tables!

感谢任何建议

Answer 1

CREATE TYPE dbo.DeptList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.RetrieveDepartments
  @dept_list AS dbo.DeptList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Name FROM dbo.table1 WHERE ID IN (SELECT ID FROM @dept)
  UNION ALL 
  SELECT Name FROM dbo.table2 WHERE ID IN (SELECT ID FROM @dept)
  -- ...
END
GO

现在在您的 C# 代码中, 请创建一个数据表, 填入 ID, 并将它传送到存储程序。假设您已经有一份名为临时列表的清单, 并且 ID 存储在 ID 中 :

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID"));

foreach(var item in tempList)
{ 
    tvp.Rows.Add(item.id); 
}

using (connObject)
{
    SqlCommand cmd = new SqlCommand("StoredProcedure", connObject);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@dept_list", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    ...
}

您也可以使用分割函数。许多函数存在, 如果您可以保证输入是安全的, 这是我喜欢的。 (没有 & lt;, & gt;, & amp; 等) :

CREATE FUNCTION dbo.SplitInts_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN 
   (  
      SELECT Item = y.i.value( (./text())[1] ,  int )
      FROM 
      ( 
        SELECT x = CONVERT(XML,  <i>  
        + REPLACE(@List, @Delimiter,  </i><i> ) +  </i> ).query( . )
      ) AS a 
      CROSS APPLY x.nodes( i ) AS y(i)
   );
GO

现在,您的程序可以是:

CREATE PROCEDURE dbo.RetrieveDepartments
  @dept_list VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH d AS (SELECT ID = Item FROM dbo.SplitInts(@dept_list,  , ))
  SELECT Name FROM dbo.table1 WHERE ID IN (SELECT ID FROM d)
  UNION ALL
  SELECT Name FROM dbo.table2 WHERE ID IN (SELECT ID FROM d)
  -- ...
END
GO

友情链接