CODEWITHSUNDEEP
sql-server
The Furious Bear
The Furious Bear

Reputation: 572

ISNULL wildcard '%' search returning no records

As one of the criteria of a WHERE clause in a search query in SQL Server, I'm trying to search by courseId using ISNULL.

When the query is executed with all null values except for @currentUserId, as below, the query returns no records.

My SQL:

    DECLARE @currentUserId int = '6'
    DECLARE @searchString nvarchar(MAX) = NULL
    DECLARE @courseId nchar(10) = NULL
    DECLARE @dateFrom date = NULL
    DECLARE @dateTo date = NULL


    SELECT [questionId],[Question].[moduleId],[questionDate],[questionTopic]
    FROM [dbo].[Question],[dbo].[Module],[dbo].[ModuleCourse]
    WHERE [lecturerId] = @currentUserId AND

    ([dbo].[Question].[moduleId] = [dbo].[Module].[moduleId] AND
    [dbo].[Module].[moduleId] = [dbo].[ModuleCourse].[moduleId]) AND

    ([dbo].[Question].[questionTopic] LIKE ISNULL('%' + @searchString + '%','%') OR
    [dbo].[Question].[questionText] LIKE ISNULL('%' + @searchString + '%','%')) AND

    [dbo].[ModuleCourse].[courseId] = ISNULL(@courseId,'%') AND

    ([dbo].[Question].[questionDate] >= ISNULL(@dateFrom,(SELECT MIN([questionDate])
                                                                FROM [dbo].[Question])) AND
    [dbo].[Question].[questionDate] <= ISNULL(@dateTo,(SELECT(GETDATE()))))

When the line

    [dbo].[ModuleCourse].[courseId] = ISNULL(@courseId,'%') AND

is commented out, results return as expected. ([dbo].[ModuleCourse].[courseId] is PK; nchar(10))

It seems as though the wildcard % does not work in this situation; I have no idea why. Please help...

Upvotes: 2

Views: 1124

Answers (1)

Guffa
Guffa

Reputation: 700212

If @courseId is null, you will be comparing the courseId to the string '%'. The = operator doesn't use wildcards.

You can use like instead of =, or write the condition as:

(@courseId is null or [dbo].[ModuleCourse].[courseId] = @courseId) AND

Upvotes: 4

Related Questions