Create Procedure select_Proc1
    @Key1 int=0,
    @Key2 int=0
As
BEGIN
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
END
GO

quote:

---

Originally posted by Bustaz Kool

[CODE]Create Procedure select_Proc1
@Key1 int=0,
@Key2 int=0
As
BEGIN
Select key3
From Or_Table
Where (@key1 =0 OR Key1 =@Key1) AND
(@key2 =0 OR Key2 =@Key2)
OPTION (RECOMPILE)
END[/CODE]
This will force the query engine to look explicitly at your parameters and generate an optimized execution plan. For instance, in the case where @key = 1, it won't bother to see if key1 = @key1 since it will already know that the OR logic will pass.

---

Select key3
From Or_Table
Where 
Key1 = isnull(nullif(@Key1,0), Key1) AND
Key2 = isnull(nullif(@Key2,0), Key2)

Select key3
From Or_Table
Where (@key1 =0) 
  AND (@key2 =0)
UNION ALL
Select key3
From Or_Table
Where (Key1 =@Key1) 
  AND (Key2 =@Key2)

SET SHOWPLAN_ALL OFF
GO

IF OBJECT_ID('tempdb..#or_table') IS NOT NULL DROP TABLE #or_table
GO

CREATE TABLE #or_table (
	[ID] INT IDENTITY (1,1) PRIMARY KEY
	, [key1] INT
	, [key2] INT
	, [key3] INT
	)
CREATE INDEX IX_OR_KEY1_KEY2 ON #or_table ([key1], [key2])
GO

INSERT #or_table ([key1], [key2], [key3])
SELECT
	CAST(CAST(NEWID() AS VARBINARY(64)) AS INT)
	, CAST(CAST(NEWID() AS VARBINARY(64)) AS INT)
	, CAST(CAST(NEWID() AS VARBINARY(64)) AS INT)
FROM
	(SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) a
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) b
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) c
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) d
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) e
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) f
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) g
GO

SELECT COUNT(*) FROM #or_table AS [Records]
GO



DECLARE @key1 INT
DECLARE @key2 INT
SET @key1 = 3
SET @key2 = 4
Select key3
    From #Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)

GO
DECLARE @key1 INT
DECLARE @key2 INT
SET @key1 = 3
SET @key2 = 4

SELECT key3
FROM
	#or_table
WHERE
	key1 = CASE @key1 WHEN 0 THEN key1 ELSE @key1 END
	AND key2 = CASE @key2 WHEN 0 THEN key2 ELSE @key2 END

GO

DECLARE @sql NVARCHAR(4000)
DECLARE @key1 INT
DECLARE @key2 INT

SET @key1 = 3
SET @key2 = 4

SET @sql = N'
SELECT
	[key3]
FROM
	#or_table
WHERE
	1=1'

IF @key1 > 0 SET @sql = @sql + N'
	AND key1 = @key1'

IF @key2 > 0 SET @sql = @sql + N'
	AND key2 = @key2'

EXEC sp_ExecuteSql
	@sql
	, N'@key1 INT
	, @key2 INT'
	, @key1
	, @key2
GO

DECLARE @key1 INT
DECLARE @key2 INT

SET @key1 = 3
SET @key2 = 4
Select key3
From #Or_Table
Where (@key1 =0) 
  AND (@key2 =0)
UNION ALL
Select key3
From #Or_Table
Where (Key1 =@Key1) 
  AND (Key2 =@Key2)