SQL Server 2000 stored procedure branching with parameters

I want to create a stored procedure. If the parameter is -1 then there should not be a where clause on that column else there should be a WHERE clause. What's the best way to do it without a lot of IF branching?

I checked the archive. There are a few similar questions but not exactly the same.

CREATE PROCEDURE report
(
  @site int,
  @promo int,
  @type int
)
AS
SET NOCOUNT ON

-- I want to avoid this:
IF @site = -1 AND @promo = -1 and @type = -1
BEGIN
  SELECT * from table
END开发者_运维问答
IF @site > -1 AND @promo = -1 and @type = -1
BEGIN
  SELECT * from table WHERE site = @site;
END
... -- other cases


ELSE  -- all parameters are > -1
BEGIN
  SELECT * from table 
  WHERE site = @site AND promo = @promo AND type = @type
END

This works in many cases, (despite what the comments will say without trying it) because the optimiser will ignore the ISNULL bit. Only works for non-null columns

SELECT @site = NULLIF(@site, -1) ...

SELECT * from table  
  WHERE site = ISNULL(@site, site) ..

Otherwise, conditional WHERE which is usually bad because OR can not be optimised

SELECT * from table  
  WHERE (@site = -1 OR site = @site) AND  (...

Or separate stored procedures (don't think you want that either)

Or use sp_executesql (avoids dynamic SQL)

How about:

SELECT * FROM table WHERE
  ((site = @site) OR (@site = -1)) AND
  ((promo = @promo) OR (@promo = -1)) AND
  ((type = @type) OR (@type = -1))

One caveat, though, you may find that SQL is not very intelligent in optimizing this sort of query.

why fight against the obvious, simplest solution?

seriously, the branching solution make the intent clear, and can easily be understood by others.