我试图进行搜索，但是无法找到任何有助于我的东西。
我正在尝试在SQL中完成这个操作：
declare @locationType varchar(50);
declare @locationID int;
SELECT column1, column2
FROM viewWhatever
WHERE
CASE @locationType
WHEN 'location' THEN account_location = @locationID
WHEN 'area' THEN xxx_location_area = @locationID
WHEN 'division' THEN xxx_location_division = @locationID
我知道我不应该在每个结尾处加上“= @locationID”，但我无法使语法正确。SQL一直抱怨我的第一个WHEN行上的“=”......
我该怎么做？
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
没有 case 语句...
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
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
*
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
)
等等，我们无法在运行时更改查询的结构，但是我们可以通过使谓词相等来覆盖它。
编辑：上述建议当然要好得多，只需忽略我的建议。
您可以在谓词中检查WHERE条件时使用WHERE子句，例如：
WHERE account_location = CASE @locationType
WHEN 'business' THEN 45
WHEN 'area' THEN 52
END
在您的情况下，您需要将查询放入存储过程中或创建三个单独的查询。
OR 运算符可以在 where 条件中作为 case when 的替代品。
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
请尝试执行此查询。
对上述帖子的回答：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
)
)
尝试一下这个查询，它非常简单和有用：已准备好执行！
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
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