June 19, 2009 at 7:34 am
I have a simple search form in MS Access (ADP) that passes parameter values into a stored procedure that returns records from a names and address table.
THIS IS THE SP -
CREATE PROCEDURE dbo.sp_Search(
@surname nvarchar(20),
@forename nvarchar(20),
@DOB smalldatetime)
AS
SET NOCOUNT ON
SELECT NHS_NUMBER, GENDER, DOB, PCT, FORENAME, SURNAME, MIDDLENAMES, PREMISES, STREET, LOCALITY, POST_TOWN, COUNTY, POSTCODE
FROM dbo.[t_POPULATION]
WHERE (SURNAME = @surname)
AND (FORENAME =@forename)
AND (DOB = @DOB)
GO
I only use 3 fields to search on - SURNAME, FORENAME and DATE of BIRTH (DOB) and this works fine if all fields have an entry however if I only use SURNAME and FORENAME and don't enter a value for DOB then the query doesn't return any records because obviously there aren't any records that meet the criteria.
I need to know how to put a condition into the sp that deals with these scenarios -
SURNAME and FORENAME entered and no DOB
SURNAME entered and no FORENAME and no DOB
FORENAME entered and no SURNAME and no DOB (unlikely search)
DOB entered and FORENAME entered but no SURNAME (unlikely search)
DOB entered and SURNAME entered and no FORENAME
DOB entered and no SURNAME and no FORENAME
Presumably it's some kind of CASE statement that runs a slightly different SELECT query depending on which search field(s) I pick?
Any advice is much appreciated.
June 19, 2009 at 7:46 am
It's definitely cleaner to do it all with boolean logic in the where clause.
For example, assuming you've got defaults of '' for character params and NULL for @DOB:
WHERE (SURNAME = @surname OR @surname = '')
AND (FORENAME =@forename OR @forename = '')
AND (DOB = @DOB OR @DOB IS NULL)
AND (@surname>'' OR @forename>'' OR @DOB IS NOT NULL)
This would allow any field to be blank, but require at least one to be filled. I'd take it a step further and make it work with wildcards:
WHERE (SURNAME LIKE @surname + '%')
AND (FORENAME LIKE @forename + '%')
AND (DOB = @DOB OR @DOB IS NULL)
AND (@surname>'' OR @forename>'' OR @DOB IS NOT NULL)
June 19, 2009 at 8:44 am
Thanks very much for that "Boolean is best" then!
I'll try it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply