Access/SQL Server Stored Procedure Search Query - Null values

Question

I have transferred my Access data tables to SQL server, whilst still wanting to use Access for the front end. This all worked well, but has since made the queries run slower. The one that runs slowest is a query to search for records.

Therefore I am in the process of converting the query into a SQL stored procedure, and it is almost working, from help I have had here. The search form currently has 2 search fields (firstname and surname), but this will expand to more search fields once I can resolve the issue.

The issue I am facing is dealing with null values in the search fields. For example if the user enters a string of text in both the firstname field and surname field the query returns the correct results. However, if either f the search fields are left blank. The query does not work, and you get an error debugging message.

SQL Stored procedure:

ALTER PROCEDURE [dbo].[ZSearch]

@FirstName varchar(100), @Surname varchar(100)

AS BEGIN

SELECT [First name], [Surname] FROM [Names] Where ([First Name] LIKE '%' + @FirstName + '%') and ([Surname] LIKE '%' + @Surname + '%')

END

Then in Access, I have a pass through query which is:

EXEC ZSearch

Then Finally, on my form, I have a button which runs the following VB on click:

Private Sub Command4_Click() Dim rst As DAO.Recordset

Dim Firstname As String
Dim Surname As String

Firstname = [Forms]![Search]![SearchFirstName]
Surname = [Forms]![Search]![SearchSurname]
   
With CurrentDb.QueryDefs("qryPassR")
    .SQL = "EXEC ZSearch " & Firstname & ", " & Surname 'Debug.Print .SQL
    Set rst = .OpenRecordset()
    DoCmd.OpenQuery "qryPassR" End With End Sub

Please could someone assist me with how to deal with null values in the search fields. I think it needs to say that if the search field is blank, treat it as a wildcard, and if it isn't blank then use what is in there.

Many Thanks

iman kazemi iman kazemi · Accepted Answer · 2020-07-22T12:52:57

You can use ISNULL or you can add where clauses like this:

-- WHERE ([First Name] LIKE '%' + ISNULL(@FirstName, '') + '%')
WHERE (@FirstName IS NULL OR [First Name] LIKE '%' + @FirstName + '%')

Access/SQL Server Stored Procedure Search Query - Null values

1 Answers