CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Tim D'hoe
Tim D'hoe

Reputation: 33

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

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:

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

Upvotes: 2

Views: 315

Answers (2)

Jason Whitish
Jason Whitish

Reputation: 1438

Your WHERE statement has its AND and OR conditions arranged in such a way that they are evaluating incorrectly for what I assume you want them to do. You can fix it by encapsulating your OR statments to account for the correct parts. Example:

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))

EDIT: Looking at the same data, there's an additional problem here. You're trying to check equality on Company name to COALESCE(@companyname, d.companyname), but you have cases where your CompanyName is NULL. When a value is NULL, it's unknown, so SQL won't treat it as being equal, even to itself.

This is one reason I generally prefer, rather than the COALESCE syntax above to do somethng like this:

AND (@companyname IS NULL OR d.CompanyName = @companyname)

The above doesn't care if the stored value for company name is null if the parameter is also null (and if the parameter isn't null, your equality still works out).

Upvotes: 4

BlakeH
BlakeH

Reputation: 3494

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)

Upvotes: 5

Related Questions