Вопрос по c#, sql-server – Параметры к предложению EXISTS в хранимой процедуре

1

У меня есть столDEPT, который содержит 2 столбца -ID, NAME.

Форма поиска представлена с идентификаторами изDEPT таблица и пользователь может выбрать любое количество идентификаторов и отправить форму, чтобы получить связанныеNAMEs.

Разъяснение / Входы:

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

Любые другие решения, чтобы продолжить?

НОТА:
Этот пример прост с 1 таблицей - в реальной жизни мне приходится иметь дело с более чем 6 таблицами!

Спасибо за любые предложения

Какова актуальностьEXISTS в вашем названии? Dan Puzey
Что вы подразумеваете под динамическим запросом, который не является управляемым? Было бы проще сказатьIN (1,2,4,5) затем построить табличный параметр. mellamokb
возможный дубликатPassing an array of parameters to Stored Procedure StingyJack
Можете ли вы использовать параметры XML? (версия SQL не указана) StingyJack
Мне не нравится предложенный дубликат - в принятом ответе используется метод документа XML, и ни в одном из ответов не используются табличные параметры.How about this one instead? Ему нужно только изменить его с INSERT / SELECT на SELECT ... WHERE IN. Aaron Bertrand

Ваш Ответ

1   ответ
8
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 # создайте DataTable, заполните его идентификаторами и передайте его хранимой процедуре. Предполагая, что у вас уже есть список с именем tempList и идентификаторы хранятся в 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
Я знаю, что это то, о чем просил ОП (так что +1), но что за безобразный беспорядок - для этого EF потребовалось бы 4-строчный класс и лямбду. Я не могу не думать, что это делает это трудным путем ради этого.
Трудно для одних, просто для других. Это похоже на многие вещи - то, что работает лучше для вас, не обязательно то, что каждый хочет.
Потому что я оптимизирую на уровне базы данных и хочу иметь возможность делать это даже после развертывания приложения, без необходимости перекомпиляции и повторного развертывания приложения. Также хорошо, если несколько приложений совместно используют функциональные возможности, они могут вызывать одну и ту же хранимую процедуру вместо того, чтобы у каждого была своя собственная копия одного и того же запроса SQL (и среднего уровня, и проверки, и т. Д. И т. Д.). Это довольно просто на самом деле и (по крайней мере для меня) не имеет ничего общего с безопасностью или повторным использованием плана (хотя последнее может быть серьезной проблемой с ранними версиями Linq to SQL). Еще одна причина: есть много вещей, которые вы можете сделать
в хранимой процедуре, что EF и другие вещи ORM просто не транслируются хорошо. Кто-то на днях пытался преобразовать правильное объединение, и один из экспертов EF сказал: «Вы не можете этого сделать»; переписать его как левое соединение. & quot; Я не знаю, правда это или нет, но я виделmany в случаях StackOverflow, когда EF / Linq и т. д. создавали значительные препятствия на пути к распространенным проблемам, которые тривиально решить в T-SQL, но это "полезно" строители отношений не понимают.
Я понимаю, что - в противном случае у меня не было бы + 1 - d - это то, о чем просил ФП, так как вы ответили, но мне любопытно, почему кто-то выбрал бы этот метод, если его не принуждают? Я не вижу каких-либо преимуществ для безопасности (SP не являются более безопасными, чем параметризованные Q), возможное преимущество в производительности, поскольку план выполнения используется повторно, но это также может быть сделано более читабельно / с меньшим количеством кода с использованием скомпилированных запросов. и т. д. Вы так делаете по своему выбору? Если это так, не могли бы вы сказать мне, почему? Возможно, я просто что-то упустил

Похожие вопросы