Stored procedure (SQL Server), can't find the issue

Question

This stored procedure is use for searching records.

When I only fill in the parameter @ID or @FirstName, it works. But not if I only fill in @LastName.

For example:

• @ID = 1, the rest is NULL --> should give 1 row --> RESULT: 1 row (ok)

• @ID = NULL, @FirstName = 'Tim', the rest is NULL --> should give 1 row --> RESULT: 1 row (ok)

• @ID = NULL, @FirstName = NULL, @LastName = 'BlaBla', the rest is NULL --> should give 1 row --> RESULT: all rows (Not OK)

Anyone know why?

Thanks in advance.

This is my procedure:

ALTER PROCEDURE lookupSubscriber 
  -- Add the parameters for the stored procedure here
  @ID int,
  @firstname nvarchar(50), 
  @lastname nvarchar(60),
  @street nvarchar(80),
  @housenumber nvarchar(6),
  @companyname nvarchar(50),
  @city nvarchar(50),
  @ResultString nvarchar(80) OUTPUT,
  @ResultValue int OUTPUT,
  @ResultCount int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Replacing empty strings with NULL
IF @ID = 0 BEGIN SET @ID = NULL; END
IF @firstname = '' BEGIN SET @firstname = NULL; END
IF @lastname = '' BEGIN SET @lastname = NULL; END
IF @street = '' BEGIN SET @street = NULL; END
IF @companyname = '' BEGIN SET @companyname = NULL; END
IF @housenumber = '' BEGIN SET @housenumber = NULL; END
IF @city = '' BEGIN SET @city = NULL; END

    -- Insert statements for procedure here
BEGIN TRY
    SELECT s.ID, COALESCE(d.FirstName,'NONE'), COALESCE(d.LastName,'NONE'), d.Street, COALESCE(d.CompanyName,'NONE'), d.HouseNumber, c.name
    FROM Subscriber s
    INNER JOIN SubscriberDetail d ON d.ID = s.Detail_ID
    INNER JOIN City c ON d.City_ID = c.ID
    WHERE (s.ID = COALESCE(@ID, s.ID)
        AND d.FirstName = COALESCE(@firstname, d.FirstName) OR d.FirstName = 'NONE'
        AND d.LastName = COALESCE(@lastname, d.LastName) OR d.LastName = 'NONE'
        AND d.Street = COALESCE(@street, d.Street)
        AND d.CompanyName = COALESCE(@companyname, d.CompanyName) OR d.CompanyName = 'NONE'
        AND d.HouseNumber = COALESCE(@housenumber, d.HouseNumber)
        AND c.name = COALESCE(@city, c.name))

    SET @ResultCount = @@ROWCOUNT
    SET @ResultString = 'Lookup successful'
    SET @ResultValue = 0
END TRY
BEGIN CATCH
    SET @ResultString = 'ERROR: ' + ERROR_MESSAGE()
    SET @ResultValue = 2
END CATCH
END
GO

Example data:

Subscriber:

ID = 1 | Type_ID = 1 | Detail_ID = 2

ID = 2 | Type_ID = 2 | Detail_ID = 3

SubscriberDetail:

ID = 1 | FirstName = 'Laurens' | LastName = 'De Neys' | CompanyName = NULL | Street = 'Ergens' | HouseNumber = 2 | City_ID = 1

ID = 2 | FirstName = 'Tim' | LastName = 'Blabla' | CompanyName = NULL | Street = 'Iets' | HouseNumber = 26 | City_ID = 2

City:

ID = 1 | name = 'Liedekerke' | postalCode = 1770

ID = 1 | name = 'Leuven' | postalCode = 3000

Answer 1

Perhaps you need to put your OR conditions in parentheses with your original conditions?

For all of these expressions:

AND d.LastName = COALESCE(@lastname, d.LastName) OR d.LastName = 'NONE'

Change to

AND (d.LastName = COALESCE(@lastname, d.LastName) OR d.LastName = 'NONE')

EDIT

Well, I can't know what is in your DB, but I have a feeling some of that logic is incorrect. Try this:

SELECT s.ID, COALESCE(d.FirstName,'NONE'), COALESCE(d.LastName,'NONE'), d.Street, COALESCE(d.CompanyName,'NONE'), d.HouseNumber, c.name
    FROM Subscriber s
    INNER JOIN SubscriberDetail d ON d.ID = s.Detail_ID
    INNER JOIN City c ON d.City_ID = c.ID
    WHERE (@id is null or s.ID = @ID)
        AND (@firstname is null or d.FirstName = @firstname)
        AND (@lastname is null or d.LastName = @lastname)
        AND (@street is null or d.Street = @street)
        AND (@companyname is null or d.CompanyName = @companyname)
        AND (@housenumber is null or d.HouseNumber = @housenumber)
        AND (@city is null or c.name = @city)