How to construct the logic in this SQL WHERE clause?-CodePudding

I am trying to write a stored procedure to search through text fields of a table as follows:

TABLE: [User]

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

The production database has many columns and a very large data set. The point of this SP is to speed up searching as much as practically possible.

What I tried:

EntityFrameworkCore LINQ queries.
Generating SQL on-the-fly using ADO .NET.
The stored procedure below.

The SP has yeilded the best results so far but the results are not accurate.

TEST SCRIPT

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', '@'

RESULTS:

The first 4 queries should have returned ALL rows.

Query 1: Expected Rows: 3, Returned Rows: 0.
Query 2: Expected Rows: 3, Returned Rows: 0.
Query 3: Expected Rows: 3, Returned Rows: 0.
Query 4: Expected Rows: 3, Returned Rows: 0.
Query 5: Expected Rows: 1, Returned Rows: 3.
Query 6: Expected Rows: 3, Returned Rows: 3.

STORED PROCEDURE:

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

There are two questions here:

What am I doing wrong in the SP logic?
Is this the most performant way to condition on the WHERE clause? I am not sure if CURSORs apply in this context.

Any advice would be appreciated.

CodePudding user response：

Your logic is wrong: you need @UseName = 0 and @UseEmail = 0 in the AND half of the procedure. You also need to swap (@UseName = 1) OR to (@UseName = 1) AND in the OR half.

Although it's hard to say what the intention would be for @Condition if only one search value is supplied: what if [Name] or [Email] in the row is null?

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

Performance-wise: this is never going to be great, because of the leading wildcard.

You may also get parameter-sniffing problems, for which the solution is usually to build the query dynamically, as I show in an answer to your other question.

CodePudding user response：

Question 1

I think the problem is around the OR predicates inside the parenthesis in each condition, I believe it should be an AND predicate like shown below:

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

Question 2

I agree with Larnu's comment that, because you're using those wildcards, you probably can't work much on performance.

CodePudding user response：

@UseName and @UseEmail are not necessary. you can simply null the variable if it's a whitespace, and then make use of ISNULL under the WHERE along with the @Condition as well. (no need for the IF/ELSE

Here is the procedure wearing a swimming goggles :) :

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