SQL Server中是否存在条件运算符?

m1m5dgzv  于 2022-12-10  发布在  SQL Server
关注(0)|答案(3)|浏览(160)

我有下面的SQL代码片段,我想通过操作@Customer参数的值来选择所有客户的销售额。在SQL Server 2008 R2中有什么方法可以做到这一点吗?我曾尝试设置@Customer = '%',但显然没有效果,因为没有LIKE运算符。
我只想操作参数,因为在其他情况下,我只需要选择一个客户。在WHERE子句中是否有使用IF/ELSE这样的语句?

DECLARE @Customer varchar(5) = ''

SELECT *
FROM SalesData
WHERE Customer=@Customer
w9apscun

w9apscun1#

Is there such a thing as using an IF/ELSE in the WHERE clause
Absolutely, there is a CASE expression - not only in the WHERE clause, but also in other parts of your query. However, a more common approach is to use logical expressions in the WHERE clause that force SQL Server to take one condition or the other depending on the parameter setting.
my condition would be if @Customer = '' then select all
If you would like to select all customers when the parameter is set to empty, or select all customers where the parameter is not set, you can do this:

SELECT *
FROM SalesData
WHERE @Customer = ''
   OR  Customer = @Customer

If @Customer is set to '' , the first clause of the OR expression will be TRUE for all rows, so all customers would be returned. However, when @Customer is non-empty, the second part of the expression would be evaluated.

6qfn3psc

6qfn3psc2#

Not quite sure how you need to perform search
But can try something like below

SELECT *
FROM SalesData
WHERE Customer like '%' + @Customer + '%'

Or

SELECT *
FROM SalesData
WHERE (LEN(@Customer)>0 AND Customer =@Customer)
     OR (LEN(@Customer)=0)

In here I havent trim assuming you always pass '' . But you can always use LEN(LTRIM(RTRIM(@Customer)))

xwmevbvl

xwmevbvl3#

如果将@Customer默认为NULL而不是'',则只需

WHERE Customer = ISNULL(@Customer, Customer)

相关问题