T-SQL 根据输入参数的值使用不同的 WHERE 子句 [英] T-SQL use different WHERE clause based on value of input parameter

问题描述

我想根据输入参数的值更改查询的 WHERE 子句.我猜这是可能的，但似乎我以错误的方式接近它?

I would like to change the WHERE clause of a query based upon the value of an input parameter. I'm guessing this is possible, but it seems that I'm approaching it in the wrong way?

我的 SP 查询的简化版本:

A simplified version on my SP query:

CREATE PROCEDURE [dbo].[GetMailboxMessagesByStatus]
    @UserId             UNIQUEIDENTIFIER,
    @MessageStatus      INT
AS
BEGIN
    SELECT  * 
    FROM  MailboxMessages m
    WHERE
    CASE @MessageStatus
        WHEN 4 THEN m.SenderId = @UserId    --Sent
        ELSE m.RecipientId = @UserId        --Inbox
    END
END 
GO

推荐答案

无需 case 或 iif 结构:

No need for case or iif constructs:

WHERE @MessageStatus = 4  AND  m.SenderId = @UserId
   OR @MessageStatus <> 4 AND  m.RecipientId = @UserId

当被查询的表非常大时，请注意使用此构造的大表.使用 IF 语句(例如 Chester Lim)使用 2 个单独的查询可能是更好的解决方案.防止参数嗅探也可能是个好主意

Be aware on big tables using this construct when the table being queried is quite large. Using 2 seperate queries using a IF statement like Chester Lim suggested might be the better solution. Preventing parameter sniffing might also be a good idea

这篇关于T-SQL 根据输入参数的值使用不同的 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！