参数给EXISTS子句在存储过程 [英] Parameters to the EXISTS clause in a stored procedure

问题描述

我有一个表 DEPT ，其中包含两列 - ID，名称

一个搜索表单呈现从 DEPT 表中的ID和用户可以选择任意数量的ID并提交表单，得到相关姓名

澄清/输入：

• 我不希望建立一个动态查询 - 它不是管理


• 我使用表值参数的偏爱存储过程

任何其他解决方案进行

请注意：结果
这个例子是简单的用1台 - 在现实生活中，我要处理超过6桌

感谢您的任何建议。

解决方案

<预类=郎-SQL prettyprint-覆盖> CREATE TYPE dbo.DeptList
如表
（
ID INT
）;
GO

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

选择的名字从dbo.table1 WHERE ID IN（SELECT ID FROM @dept）
UNION ALL
选择的名字从dbo.table2 WHERE ID IN（SELECT ID FROM @dept）
- ...
端
GO


现在在C＃代码，创建一个DataTable，用的ID填写它，并把它传递到存储过程。假设你已经有一个名为tempList列表和ID存储在ID：

<预类=郎-CS prettyprint-覆盖> DataTable的TVP =新的DataTable（）;
tvp.Columns.Add（新的DataColumn（ID））;

的foreach（在tempList VAR项）
{
tvp.Rows.Add（item.id）;使用（connObject）
{
的SqlCommand CMD =新的SqlCommand（StoredProcedure的connObject）
}

;
cmd.CommandType = CommandType.StoredProcedure;
的SqlParameter tvparam = cmd.Parameters.AddWithValue（@ dept_list，TVP）;
tvparam.SqlDbType = SqlDbType.Structured;
：
}


您还可以使用拆分功能。许多存在的，这是我喜欢的，如果你能保证输入是安全的（无<，>，&安培;等）：

<预类=朗SQL prettyprint-覆盖> CREATE FUNCTION dbo.SplitInts_XML
（
@list VARCHAR（MAX），
@Delimiter CHAR（1）
）
RETURNS TABLE
AS
返回
（
选择项目= yivalue（'（./文（））[1]'，'廉政'）
从
（
选择X = CONVERT（XML，'< I>'
+ REPLACE（@list，@Delimiter，'< I> /;< I>'）+' < /我方式>'）查询（''）
）作为一个
CROSS APPLY x.nodes（I）为Y（I）
）;
GO


现在你的程序可以是：

<预类=郎-SQL prettyprint-覆盖> CREATE PROCEDURE dbo.RetrieveDepartments
@dept_list VARCHAR（MAX），
AS
BEGIN
SET NOCOUNT ON;

;以D AS（SELECT ID = FROM dbo.SplitInts（@dept_list，项目'，'））
从dbo.table1 SELECT name，其中ID IN（SELECT ID FROM D）
UNION ALL
选择的名字从dbo.table2 WHERE ID IN（SELECT ID FROM D）
- ...
端
GO


I have a table DEPT, which holds 2 columns - ID, NAME.

A search form is presented with the IDs from the DEPT table and the user can chose any number of IDs and submit the form, to get the related NAMEs.

Clarification/Inputs:

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

Any other solutions to proceed?

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

Thanks for any suggestions

解决方案

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

Now in your C# code, create a DataTable, fill it in with the IDs, and pass it in to the stored procedure. Assuming you already have a list called tempList and the IDs are stored in 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;
    ...
}

You can also use a split function. Many exist, this is the one I like if you can guarantee that the input is safe (no <, >, & etc.):

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

Now your procedure can be:

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

这篇关于参数给EXISTS子句在存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！