OR_test_step1

create_or_proc_originalProposedSolution

create procedure test2
    @id int = 0,
    @col1 int = 0,
    @col2 int = 0
as
begin
    set nocount on
    declare @sql nvarchar(255), @cond nvarchar(255)
    set @sql = 'select id, col1, col2 from or_test '
    set @cond = ''
    if @id <> 0 set @cond = 'where id = @id '
    if @col1 <> 0
        if @cond = ''
            set @cond = 'where col1 = @col1 '
        else
            set @cond = @cond + 'and col1 = @col1 '
    if @col2 <> 0
        if @cond = ''
            set @cond = 'where col2 = @col2 '
        else
            set @cond = @cond + 'and col2 = @col2 '
    set @sql = @sql + @cond
    exec sp_executesql @sql, N'@id int, @col1 int, @col2 int', @id, @col1,
@col2
end
go

exec test2 @id = 400000
go

create_or_proc_version1

create procedure test1
    @id int = 0,
    @col1 int = 0,
    @col2 int = 0
as
begin
    set nocount on

    select id, col1, col2
    from or_test
    where (id = @id or @id = 0)
    and (col1 = @col1 or @col1 = 0)
    and (col2 = @col2 or @col2 = 0)
end
go

exec test1 @id = 400000
go

create_or_test

set nocount on

create table or_test (id int identity primary key clustered, col1 int,
col2 int)
go

insert into or_test (col1, col2)
select convert(int, rand() * 1000000), convert(int, rand() * 1000000)
go 1000000

create index idx_or_test_col1 on or_test (col1)
go

create index idx_or_test_col2 on or_test (col2)
go