Простейшее решение не всегда самое лучшее

В одном из блогов наткнулся на интересный вариант оптимизации громоздких запросов.

Автор предлагает запрос с большим количеством условий после WHERE

CREATE PROC find_customer
@name nvarchar(128) = NULL,
@id int = NULL
AS
BEGIN
SELECT [id], [name], [address], [creation_date]
FROM [customers]
WHERE (@name IS NULL OR [name] = @name)
AND (@id IS NULL OR [id] = @id);
END;

заменить более разветвленной конструкцией из нескольких, более простых запросов

CREATE PROC find_customer
@name nvarchar(128) = NULL,
@id int = NULL
AS
BEGIN
IF (@name IS NULL)
BEGIN
SELECT [id], [name], [address], [creation_date]
FROM [customers]
WHERE [id] = @id;
END
ELSE IF (@id IS NULL)
BEGIN
SELECT [id], [name], [address], [creation_date]
FROM [customers]
WHERE [name] = @name;
END
ELSE BEGIN
SELECT [id], [name], [address], [creation_date]
FROM [customers]
WHERE [name] = @name AND [id] = @id;
END;
END;


Либо формировать более простой запрос динамически

CREATE PROC find_customer
@name nvarchar(128) = NULL,
@id int = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @search_query nvarchar(max);
SET @search_query = '
SELECT [id], [name], [address], [creation_date]
FROM [customers]
WHERE 1 = 1 ';

IF (@name IS NOT NULL)
BEGIN
SET @search_query = @search_query + 'AND [name] = @name';
END;

IF (@id IS NOT NULL)
BEGIN
SET @search_query = @search_query + 'AND [id] = @id';
END;

EXEC sp_executesql @search_query, N' @name nvarchar(128), @id int',
@name = @name, @id = @id;
END;