“ where”子句中的 SQL Switch/Case

declare @locationType varchar(50);
declare @locationID int;


SELECT column1, column2
FROM viewWhatever
WHERE
@locationID =
CASE @locationType
WHEN 'location' THEN account_location
WHEN 'area' THEN xxx_location_area
WHEN 'division' THEN xxx_location_division
END

我认为这是表结构存在缺陷的一个指示器。也许不同的位置类型应该在不同的表中分开，这样可以进行更丰富的查询，同时避免出现多余的列。

如果你不能改变结构，下面的方法可能会有效:

SELECT
*
FROM
Test
WHERE
Account_Location = (
CASE LocationType
WHEN 'location' THEN @locationID
ELSE Account_Location
END
)
AND
Account_Location_Area = (
CASE LocationType
WHEN 'area' THEN @locationID
ELSE Account_Location_Area
END
)

等等... ... 我们不能动态地改变查询的结构，但是我们可以通过使谓词相等来覆盖它。

编辑: 以上的建议当然更好，忽略我的吧。

这样做的问题在于，当 SQL 引擎计算表达式时，它检查 FROM 部分以提取正确的表，然后检查 WHERE 部分以提供一些基本条件，因此它不能正确地计算要检查哪个列的动态条件。

在检查谓词中的 WHERE 条件时，可以使用 WHERE 子句，如

WHERE account_location = CASE @locationType
WHEN 'business' THEN 45
WHEN 'area' THEN 52
END

因此在您的特定情况下，您需要将查询放入一个存储过程中，或者创建三个独立的查询。

给你。

SELECT
column1,
column2
FROM
viewWhatever
WHERE
CASE
WHEN @locationType = 'location' AND account_location = @locationID THEN 1
WHEN @locationType = 'area' AND xxx_location_area = @locationID THEN 1
WHEN @locationType = 'division' AND xxx_location_division = @locationID THEN 1
ELSE 0
END = 1

没有案情陈述。

SELECT column1, column2
FROM viewWhatever
WHERE
(@locationType = 'location' AND account_location = @locationID)
OR
(@locationType = 'area' AND xxx_location_area = @locationID)
OR
(@locationType = 'division' AND xxx_location_division = @locationID)

请尝试这个查询。 回答以上问题:

select @msgID, account_id
from viewMailAccountsHeirachy
where
CASE @smartLocationType
WHEN 'store' THEN account_location
WHEN 'area' THEN xxx_location_area
WHEN 'division' THEN xxx_location_division
WHEN 'company' THEN xxx_location_company
END  = @smartLocation

试试这个:

WHERE (
@smartLocationType IS NULL
OR account_location = (
CASE
WHEN @smartLocationType IS NOT NULL
THEN @smartLocationType
ELSE account_location
END
)
)

试试这个问题，很容易理解:

CREATE TABLE PersonsDetail(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
GO


INSERT INTO PersonsDetail VALUES(N'Gourav', N'Bhatia', 2),
(N'Ramesh', N'Kumar', 1),
(N'Ram', N'Lal', 2),
(N'Sunil', N'Kumar', 3),
(N'Sunny', N'Sehgal', 1),
(N'Malkeet', N'Shaoul', 3),
(N'Jassy', N'Sohal', 2);
GO


SELECT FirstName, LastName, Gender =
CASE GenderID
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
ELSE 'Unknown'
END
FROM PersonsDetail

Case Statement in SQL Server Example


Syntax


CASE [ expression ]


WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n


ELSE result


END


Example


SELECT contact_id,
CASE website_id
WHEN 1 THEN 'TechOnTheNet.com'
WHEN 2 THEN 'CheckYourMath.com'
ELSE 'BigActivities.com'
END
FROM contacts;


OR


SELECT contact_id,
CASE
WHEN website_id = 1 THEN 'TechOnTheNet.com'
WHEN website_id = 2 THEN 'CheckYourMath.com'
ELSE 'BigActivities.com'
END
FROM contacts;

OR 运算符可以在条件满足时替换大小写

ALTER PROCEDURE [dbo].[RPT_340bClinicDrugInventorySummary]
-- Add the parameters for the stored procedure here
@ClinicId BIGINT = 0,
@selecttype int,
@selectedValue varchar (50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
drugstock_drugname.n_cur_bal,drugname.cdrugname,clinic.cclinicname


FROM drugstock_drugname
INNER JOIN drugname ON drugstock_drugname.drugnameid_FK = drugname.drugnameid_PK
INNER JOIN drugstock_drugndc ON drugname.drugnameid_PK = drugstock_drugndc.drugnameid_FK
INNER JOIN drugndc ON drugstock_drugndc.drugndcid_FK = drugndc.drugid_PK
LEFT JOIN clinic ON drugstock_drugname.clinicid_FK = clinic.clinicid_PK


WHERE   (@ClinicId = 0 AND 1 = 1)
OR  (@ClinicId != 0 AND drugstock_drugname.clinicid_FK = @ClinicId)


-- Alternative Case When You can use OR
AND ((@selecttype = 1 AND 1 = 1)
OR  (@selecttype = 2 AND drugname.drugnameid_PK = @selectedValue)
OR  (@selecttype = 3 AND drugndc.drugid_PK = @selectedValue)
OR  (@selecttype = 4 AND drugname.cdrugclass = 'C2')
OR  (@selecttype = 5 AND LEFT(drugname.cdrugclass, 1) = 'C'))


ORDER BY clinic.cclinicname, drugname.cdrugname
END

CREATE PROCEDURE [dbo].[Temp_Proc_Select_City]
@StateId INT
AS
BEGIN
SELECT * FROM tbl_City
WHERE
@StateID = CASE WHEN ISNULL(@StateId,0) = 0 THEN 0 ELSE StateId END ORDER BY CityName
END

尝试这个查询，它非常简单和有用: 它准备执行！

USE tempdb
GO


IF NOT OBJECT_ID('Tempdb..Contacts') IS NULL
DROP TABLE Contacts


CREATE TABLE Contacts(ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO Contacts (ID, FirstName, LastName)
SELECT 1, 'Omid', 'Karami'
UNION ALL
SELECT 2, 'Alen', 'Fars'
UNION ALL
SELECT 3, 'Sharon', 'b'
UNION ALL
SELECT 4, 'Poja', 'Kar'
UNION ALL
SELECT 5, 'Ryan', 'Lasr'
GO
 

DECLARE @FirstName VARCHAR(100)
SET @FirstName = 'Omid'
 

DECLARE @LastName VARCHAR(100)
SET @LastName = ''
 

SELECT FirstName, LastName
FROM Contacts
WHERE
FirstName = CASE
WHEN LEN(@FirstName) > 0 THEN  @FirstName
ELSE FirstName
END
AND
LastName = CASE
WHEN LEN(@LastName) > 0 THEN  @LastName
ELSE LastName
END
GO

这招对我很管用。

四位数的世纪是由规则决定的，如果一年是50或更多，则世纪是1900年，否则是2000年。

给定两个6位数的周期来标记开始和结束周期，比如一个季度，返回在该范围内的行。