Необязательные параметры TSQL, которые работают с тем же полем

У меня есть хранимая процедура, в которой я передаю тип продукта (prod_type) в качестве параметра до четырех раз. Потенциально каждый из этих параметров может быть нулевым, и в этом случае их следует игнорировать. У них должен быть оператор ИЛИ между каждым типом продукта, поэтому мы собираем все запрошенные типы продуктов.

Если возможно, я хочу избежать использования динамического SQL, передаваемого через sp_ExecuteSQL (я знаю, что это будет легко).

Упрощенная, изолированная версия того, что я пытаюсь сделать, приведена ниже для обсуждения:

CREATE TABLE #Products (Prod_ID int, prod_type int);

INSERT INTO #Products
SELECT 1, 2
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 3, 3
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 4, 5

DECLARE @prod_type1 as int;
DECLARE @prod_type2 as int;
DECLARE @prod_type3 as int;
DECLARE @prod_type4 as int;

SET @prod_type1 = NULL;
SET @prod_type2 = 2;
SET @prod_type3 = NULL;
SET @prod_type4 = 3;

Select * from #Products
WHERE ((prod_type = ISNULL(@prod_type1,prod_type)) 
OR (prod_type = ISNULL(@prod_type2,prod_type)) 
OR (prod_type = ISNULL(@prod_type3,prod_type) 
OR (prod_type = ISNULL(@prod_type4,prod_type))))

Где то, что я хотел бы выполнить, выглядит примерно так:

Select * from [Product].[Product]
WHERE (prod_type = 2 OR prod_type = 3)

Очевидно, что описанный выше обходной путь с ISNULL не будет работать, поскольку метод ISNULL будет сравнивать тип продукта каждой строки с самим собой, что приведет к положительному «попаданию». COALESCE будет иметь ту же проблему.

Может ли кто-нибудь предложить решение, не связанное с использованием sp_ExecuteSQL?


sql-server stored-procedures tsql coalesce sp-executesql
person MagicalArmchair    schedule 13.11.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Попробуйте изменить себя Where Clause вот так.

Select * 
from 
#Products
WHERE prod_type in(@prod_type1,@prod_type2,@prod_type2,@prod_type4)
person Pரதீப்    schedule 13.11.2014

arrow_upward
1
arrow_downward

Попробуй это:

DECLARE @ProdTypes TABLE (Prod_Type INT NOT NULL);

INSERT INTO @ProdTypes (Prod_Type) VALUES (2);
INSERT INTO @ProdTypes (Prod_Type) VALUES (3);

SELECT pr.*
FROM #Products pr
WHERE EXISTS (SELECT 1
              FROM @ProdTypes pt
              WHERE pt.Prod_Type = pr.Prod_Type);

Использование EXISTS допускает то, что логически является условием OR. И EXISTS завершится, если будет найдено совпадение, поэтому он не будет проходить через все строки в @ProdTypes, если только:

  • Нет подходящих значений
  • Соответствующее значение является последним в наборе ;-)

Но много раз он будет выходить до сканирования всей табличной переменной.

EDIT:
Кроме того, вы не должны передавать несколько параметров @Prod_TypeX в свою процедуру. Вместо этого передайте список целых чисел в формате CSV и используйте разделитель строк на основе SQLCLR или XML, чтобы вставить его в табличную переменную, например:

INSERT INTO @ProdTypes (Prod_Type)
   SELECT CONVERT(INT, csv.SplitVal)
   FROM   dbo.SQLCLRorXMLbasedSplitter(@ProdTypesCSV) csv;
person Solomon Rutzky    schedule 13.11.2014
comment
Так же думал. лучший подход +1. - person Rahul; 13.11.2014