filter语句中的条件值 [英] conditional values in filter statement

问题描述

你好



i有一个查询比我想在过滤器中有条件值

i am nube in sql

请帮帮我

这是我的脚本：

hello

i have a query than i want have conditional value in filter
i am nube in sql
please help me
this is my script:

DECLARE @InvCode NVARCHAR(20)
SET @InvCode = NULL

SELECT SD.* FROM [Accountting].[SaleInvoiceDetails] SD
	INNER JOIN [Accountting].[SaleInvoice] SI ON SD.[InvCode] = SI.[InvCode]
	WHERE SD.[InvCode] = (CASE WHEN @InvCode IS NULL THEN SD.[InvCode] ELSE @InvCode END)
	AND   CASE WHEN @InvCode IS NULL THEN SI.[Status] IN (1,2) ELSE SI.[Status] = SI.[Status] END

i想要，如果invcode为null，则过滤SI表中1,2的值状态，如果InvCode不为null，则不过滤任何SI，因此SI。[Status] = SI。[状态]



我该如何实施？

帮助请求e

i want if invcode is null then filter status of SI table in 1,2 value and if InvCode is not null then does not filter anything so SI.[Status] = SI.[Status]

how can i implement?
help please

推荐答案

您不能像使用控制流语句一样使用CASE。这是一个返回值的复杂函数。然后，您可以将该值与其他值进行比较。在你的情况下，最好完全删除CASE并使用简单的AND / OR来指定条件：

You cannot use CASE like it was a control flow statement. It's a complex function that returns a value. You can then compare the value to something else. In your case it would be better to drop the CASE entirely and use simple AND/OR to specify the condition:

WHERE (@InvCode IS NULL OR SD.[InvCode] = @InvCode)
  AND (@InvCode IS NOT NULL OR SI.[Status] IN (1,2))

我相信这比CASE更具可读性。

I believe this is much more readable than the CASE.

这篇关于filter语句中的条件值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！