如何構造此SQLWHERE子句中的邏輯？

我正在嘗試撰寫一個存盤程序來搜索表的文本欄位，如下所示：

表：[用戶]

[Id] BIGINT PRIMARY KEY, IDENTITY (1, 1)
[Name] NVARCHAR(100) NOT NULL
[Email] NVARCHAR(100) NOT NULL, UNIQUE

生產資料庫有很多列和一個非常大的資料集。此 SP 的目的是盡可能加快搜索速度。

我試過的：

1. EntityFrameworkCore LINQ 查詢。
2. 使用 ADO .NET 即時生成 SQL。
3. 下面的存盤程序。

到目前為止，SP 已經產生了最好的結果，但結果并不準確。

測驗腳本

USE [TestDatabase]

--DELETE FROM [User] -- Commented for your safety.
DBCC CHECKIDENT ('[User]', RESEED, 0)
INSERT INTO [User] ([Name], [Email]) VALUES ('Name 01', '[email protected]')
INSERT INTO [User] ([Name], [Email]) VALUES ('Name 02', '[email protected]')
INSERT INTO [User] ([Name], [Email]) VALUES ('Name 03', '[email protected]')

EXECUTE SpUserSearch 0
EXECUTE SpUserSearch 1
EXECUTE SpUserSearch 0, NULL, NULL
EXECUTE SpUserSearch 1, NULL, NULL
EXECUTE SpUserSearch 0, 'Name 01', '@'
EXECUTE SpUserSearch 1, 'Name 01', '@'

結果：

前 4 個查詢應該已回傳所有行。

• 查詢 1：預期行數：3，回傳行數：0。
• 查詢 2：預期行數：3，回傳行數：0。
• 查詢 3：預期行數：3，回傳行數：0。
• 查詢 4：預期行數：3，回傳行數：0。
• 查詢 5：預期行數：1，回傳行數：3。
• 查詢 6：預期行數：3，回傳行數：3。

存盤程序：

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @UseName BIT
    DECLARE @UseEmail BIT

    IF ((@Name IS NULL) OR (LEN(@Name) = 0)) SET @UseName = 0 ELSE SET @UseName = 1
    IF ((@Email IS NULL) OR (LEN(@Email) = 0)) SET @UseEmail = 0 ELSE SET @UseEmail = 1

    IF (@Condition = 0)
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) OR ([Name] LIKE '%'   @Name   '%'))
            AND
            ((@UseEmail = 1) OR ([Email] LIKE '%'   @Email   '%'))
    ELSE
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) OR ([Name] LIKE '%'   @Name   '%'))
            OR
            ((@UseEmail = 1) OR ([Email] LIKE '%'   @Email   '%'))

    RETURN (@@ROWCOUNT)

END

這里有兩個問題：

1. 我在 SP 邏輯中做錯了什么？
2. 這是以WHERE條款為條件的最有效的方式嗎？我不確定CURSORs 是否適用于這種情況。

任何意見，將不勝感激。

uj5u.com熱心網友回復：

你的邏輯是錯誤的：你需要@UseName = 0和@UseEmail = 0在AND程式的一半。您還需要交換(@UseName = 1) OR到(@UseName = 1) AND的OR一半。

雖然很難說@Condition如果只提供一個搜索值的意圖是什么：如果[Name]或[Email]在行中為空怎么辦？

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @UseName BIT
    DECLARE @UseEmail BIT

    IF ((@Name IS NULL) OR (LEN(@Name) = 0)) SET @UseName = 0 ELSE SET @UseName = 1
    IF ((@Email IS NULL) OR (LEN(@Email) = 0)) SET @UseEmail = 0 ELSE SET @UseEmail = 1

    IF (@Condition = 0)
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 0) OR ([Name] LIKE '%'   @Name   '%'))
            AND
            ((@UseEmail = 0) OR ([Email] LIKE '%'   @Email   '%'))
    ELSE
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) AND ([Name] LIKE '%'   @Name   '%'))
            OR
            ((@UseEmail = 1) AND ([Email] LIKE '%'   @Email   '%'))

    RETURN (@@ROWCOUNT)

END

性能方面：由于領先的通配符，這永遠不會很好。

您可能還會遇到引數嗅探問題，解決方案通常是動態構建查詢，正如我在您的其他問題的回答中所示。

uj5u.com熱心網友回復：

問題 1

我認為問題在于OR每個條件中括號內的謂詞，我相信它應該是一個AND如下所示的謂詞：

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @UseName BIT
    DECLARE @UseEmail BIT

    IF ((@Name IS NULL) OR (LEN(@Name) = 0)) SET @UseName = 0 ELSE SET @UseName = 1
    IF ((@Email IS NULL) OR (LEN(@Email) = 0)) SET @UseEmail = 0 ELSE SET @UseEmail = 1

    IF (@Condition = 0)
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) AND ([Name] LIKE '%'   @Name   '%'))
            AND
            ((@UseEmail = 1) AND ([Email] LIKE '%'   @Email   '%'))
    ELSE
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) AND ([Name] LIKE '%'   @Name   '%'))
            OR
            ((@UseEmail = 1) AND ([Email] LIKE '%'   @Email   '%'))

    RETURN (@@ROWCOUNT)

END

問題2

我同意 Larnu 的評論，因為您使用了這些通配符，所以您可能無法在性能上做太多作業。

uj5u.com熱心網友回復：

@UseName并且@UseEmail沒有必要。如果它是一個空格，您可以簡單地將變數設為null，然后ISNULL在 the 下和 theWHERE一起使用@Condition。（不需要IF/ELSE

這是戴泳鏡的程式:)：

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;
   -- this would covers NULL and Whitespace
    SET @Name = CASE WHEN @Name IS NOT NULL AND LEN(@Name) = 0 THEN NULL ELSE '%'   @Name   '%' END

    SET @Email = CASE WHEN @Email IS NOT NULL AND LEN(@Email) = 0 THEN NULL ELSE '%'   @Email   '%' END
    
    SELECT [Id], [Name], [Email]
    FROM 
        [User]
    WHERE
    (
            @Condition = 0 
        AND 
        (
                [Name] LIKE ISNULL(@Name, [Name])
            AND [Email] LIKE ISNULL(@Email, [Email])
        )
    )
    OR 
    (
            @Condition = 1 
        AND 
        (
                [Name] LIKE ISNULL(@Name, [Name])
            OR  [Email] LIKE ISNULL(@Email, [Email])
        )
    )

    RETURN (@@ROWCOUNT)

END

標籤：C＃ sql sql-server 存储过程

上一篇：如何在表中只有開始和結束日期值的日期范圍之間提取資料？

下一篇：SQL中的觸發器導致錯誤“Product_Reorder不是可識別的SET選項”