"or else" filter 1

[ncirkel]

Hi there,

I have a table that contains, among others, the columns idItem and idOwner. The records in this table could look like this:

idItem idOwner other_columns
1 1 this is data for user 1
1 <null> this is default data in idItem = 1 (for other users)
2 <null> there may be only default data for an idItem

I'm writing a stored procedure that accepts variables @Item and @User. I want it to return all records in the table where:
- if a record exisits with idItem = @Item and idOwner = @User, then return that record
- if that record does not exist, return record where idItem = @Item and idOwner = <null> (return default data)

Sounds fairly simple but I can't get it done!

Thanks for your help,
Nander

 

[JamesLean]

CREATE PROC apGetItem
  @item int,
  @user int
AS

IF EXISTS (
  SELECT 1 FROM tbl
  WHERE iditem = @item AND idowner = @user
)
  SELECT * FROM tbl
  WHERE iditem = @item AND idowner = @user
ELSE
  SELECT * FROM tbl
  WHERE iditem = @item AND idowner IS NULL
GO

--James

 

[ncirkel]

That was a fast one, and it works! Thanks a lot,
Nander.