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

this stored procedure use searching records.

when fill in parameter @id or @firstname, works. not if fill in @lastname.

for example:

• @id = 1, rest null --> should give 1 row --> result: 1 row (ok)

• @id = null, @firstname = 'tim', rest null --> should give 1 row --> result: 1 row (ok)

• @id = null, @firstname = null, @lastname = 'blabla', rest null --> should give 1 row --> result: rows (not ok)

anyone know why?

thanks in advance.

this procedure:

alter procedure lookupsubscriber    -- add parameters 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 begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; --replacing empty strings 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 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     subscriber s     inner join subscriberdetail d on d.id = s.detail_id     inner join city c on d.city_id = c.id     (s.id = coalesce(@id, s.id)         , d.firstname = coalesce(@firstname, d.firstname) or d.firstname = 'none'         , d.lastname = coalesce(@lastname, d.lastname) or d.lastname = 'none'         , d.street = coalesce(@street, d.street)         , d.companyname = coalesce(@companyname, d.companyname) or d.companyname = 'none'         , d.housenumber = coalesce(@housenumber, d.housenumber)         , 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 

perhaps need put or conditions in parentheses original conditions?

for of these expressions:

and d.lastname = coalesce(@lastname, d.lastname) or d.lastname = 'none' 

change

and (d.lastname = coalesce(@lastname, d.lastname) or d.lastname = 'none') 

edit

well, can't know in db, have feeling of logic incorrect. try this:

select s.id, coalesce(d.firstname,'none'), coalesce(d.lastname,'none'), d.street, coalesce(d.companyname,'none'), d.housenumber, c.name     subscriber s     inner join subscriberdetail d on d.id = s.detail_id     inner join city c on d.city_id = c.id     (@id null or s.id = @id)         , (@firstname null or d.firstname = @firstname)         , (@lastname null or d.lastname = @lastname)         , (@street null or d.street = @street)         , (@companyname null or d.companyname = @companyname)         , (@housenumber null or d.housenumber = @housenumber)         , (@city null or c.name = @city)