在SQL Server中WHERE子句没有得到正确的结果

本文关键字：结果 Server SQL WHERE 子句 | 更新日期: 2023-09-27 18:02:12

 SELECT Col1, Col2, Col3, Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 BETWEEN @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (CASE @InvoiceMethod //Problem is Here 
                  WHEN ''
                    THEN def
                  ELSE (@InvoiceMethod)
               END)

存储过程中的一段代码。如果我执行这个，它不会返回任何行，即使它有一些要返回。问题是IN子句，如果我没有传递任何东西给IN子句，即@InvoiceMethod是空的，那么我得到行。

如果我传递任何东西给@InvoiceMethod，我没有得到任何行。

@InvoiceMethod中的值为= 'A','B'

我尝试了许多组合，如'A','B'或"A","B"，但没有任何结果。

如何将值传递给IN子句?哪种格式?

请帮我解决这个问题。

将存储过程修改为

Declare @tmpt table (value nvarchar(5) not null)
      SET @InvoiceCount=(select COUNT(*) from dbo.fnSplit(@InvoiceMethod, ','))
    SET @tempVar=1;
    WHILE @tempVar<=(@InvoiceCount)
          BEGIN
        INSERT INTO @tmpt (value) 
        VALUES (@InvoiceMethod);//Here i need to insert array of values to temp   table.like invoicemethod[0],invoicemethod[1]&invoicemethod[2] depends on @InvoiceCount
        SET @tempVar=@tempVar+1;
      END
--DECLARE @tmpt TABLE (value NVARCHAR(5) NOT NULL)
--INSERT INTO @tmpt (value) VALUES (@InvoiceMethod);
 SELECT Col1,Col2,Col3,Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 between @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (SELECT value FROM @tmpt)

但是没有得到预期的结果:(

在SQL Server中WHERE子句没有得到正确的结果

在我看来，这不是解决这个问题的好方法，通过在逗号分隔的字符串中传递列的过滤值列表，因为这几乎是鼓励动态Sql方法来解决问题(即，在您执行一个构建的Sql字符串粘贴在@InvoiceMethod作为字符串)。

相反，Sql 2008有表值参数(在此之前，您可以使用Xml)，它允许您以表格式将结构化数据传递到过程中。

你只需要连接到这个表参数来影响1..N值的IN ()过滤。

CREATE TYPE ttInvoiceMethods AS TABLE
(
  Method VARCHAR(20)
);
GO
CREATE PROCEDURE dbo.SomeProc 
(
   @InvoiceMethod ttInvoiceMethods READONLY, -- ... Other Params here
)
AS
begin
   SELECT Col1, Col2, ...
   FROM Table1
        INNER JOIN @InvoiceMethod
           ON Table1.def = @InvoiceMethod.Method -- Join here
   WHERE User1 = @Owner
        ... Other Filters here
END

请查看这里的类似解决方案。

编辑

可选参数(@InvoiceMethod = '')可以通过使用子查询将JOIN更改为TVP来处理:

   WHERE
     --  ... Other filters
     AND (Table1.def IN (SELECT Method FROM @InvoiceMethod))
        OR @InvoiceMethod IS NULL)

要将TVP初始化为NULL，只需在c#中根本不绑定它

我认为in子句中不允许使用逗号表示多个值的变量。您应该使用字符串函数(分割和连接)或使用临时表解决方案。我更喜欢后者。

使用临时表来存储您的值，然后将其传递给in语句

DECLARE @tmpt TABLE (value NVARCHAR(5) NOT NULL)
INSERT INTO @tmpt .........
...
...
 SELECT Col1,Col2,Col3,Col4
   FROM Table1 
  WHERE User1 = @Owner
    AND group1 = @Group
    AND date1 BETWEEN @startDate AND @endDate
    AND Mail LIKE @email
    AND def IN (SELECT value FROM @tmpt)

使用Splitfunctions解决问题，修改SQL查询

SELECT Col1, Col2, Col3, Col4
 FROM Table1 
  WHERE User1 = @Owner
AND group1 = @Group
AND date1 BETWEEN @startDate AND @endDate
AND Mail LIKE @email
AND def IN (SELECT * FROM sptFunction(@InvoiceMethod,',')) //Problem is Here (Solved by using split functions)
相关文章：