Хранимая процедура с необязательными параметрами, не отображающая нулевые значения для объединенной таблицы

Я осмотрелся и нашел различные подходы к этой проблеме, но ни один из них не работал в моей конкретной ситуации. Я написал хранимую процедуру, которую использую для отчета SSRS, которая учитывает необязательные параметры (в SSRS я указываю список доступных параметров вместе с параметром «Все», где значение установлено на «= Ничего» ). Это работает для размещения нескольких необязательных параметров, когда, если ничего не выбрано, отображаются все записи... кроме тех, у которых значения ProjectID равны нулю.

Я хотел бы иметь возможность запустить хранимую процедуру и указать «Null» для параметра @Project и показать эти значения с нулевыми полями ProjectID, и в идеале добавить параметр «Нет» в мой отчет SSRS для этого параметра, который также покажет эти значения.

Я не совсем уверен, как изменить SP для достижения этого:

ALTER PROCEDURE [dbo].[TasksByStatus]

@Status AS Integer = NULL,
@Project AS Integer = NULL

AS

BEGIN

SELECT Task, CONVERT(VARCHAR, StartDate, 101) AS StartDate, 
      (CASE WHEN CompleteDate IS NULL THEN 'Not complete yet' 
       ELSE CONVERT(VARCHAR, CompleteDate, 101) END) AS CompleteDate, 
      (CASE WHEN Notes IS NULL THEN 'No notes provided' ELSE Notes END) AS Notes, 
       ProjectName, StatusName
FROM Tasks 
INNER JOIN Status ON Tasks.StatusID = Status.ID
LEFT JOIN Projects ON Tasks.ProjectID = Projects.ID 
AND Projects.ID IS NULL

WHERE Status.ID = ISNULL(@Status, Status.ID) 
AND Projects.ID = ISNULL(@Project, Projects.ID)
ORDER BY StartDate, StatusName

END

Результаты запроса без указания параметров:

Результаты запроса без указания параметров

Я намерен при указании NULL для @Project видеть только одну запись со значением NULL ProjectID.

Изменить для дальнейшего уточнения

Для OP, пожалуйста, сообщите нам, какими должны быть точные результаты для каждой строки в данной таблице. Кроме того, когда вы упоминаете «Нет», как бы вы хотели передать это хранимой процедуре? (Переменная @Project определена как целое число)

Parameter  Projects.ID  In Result
-----------------------------------------
1          1            Yes  
1          2            No  
1          NULL         No
'None'     1            No
'None'     2            No 
'None'     NULL         Yes
NULL       1            Yes
NULL       2            Yes
NULL       NULL         Yes

sql-server stored-procedures reporting-services left-join optional-parameters
person Zogglet    schedule 08.03.2012    source источник
comment
В вашем запросе есть как AND Projects.ID IS NULL, так и AND Projects.ID = ISNULL(@Project, Projects.ID). Разве это не противоречиво?   -  person ruakh    schedule 08.03.2012
comment
Да, это я тоже поймал. Я удалил AND Projects.ID IS NULL. Я использовал это для тестирования.   -  person Zogglet    schedule 08.03.2012
comment
К сожалению, подход COALESCE не изменил результатов.   -  person Zogglet    schedule 08.03.2012
comment
@Zogglet - я уверен, что вы стараетесь изо всех сил, но отсутствие ответов ясно указывает мне на то, что у многих людей возникают проблемы с вашими требованиями. Я добавил к вашему ответу таблицу истинности. Возможно, если вы его завершите, то что-то прояснится.   -  person Lieven Keersmaekers    schedule 08.03.2012
comment
Я заполнил вашу таблицу истинности и, отвечая на ваш вопрос о передаче None, я имел в виду реализацию этой хранимой процедуры как набора данных, используемого в SSRS. Я добавил опцию None к доступным параметрам со значением NULL. Однако у меня есть еще одна опция All, которая передает =Nothing в параметр, который успешно показывает все записи, кроме тех, которые имеют нулевые значения ProjectID. Это проблема. Это что-то проясняет?   -  person Zogglet    schedule 08.03.2012

Ответы (2)


arrow_upward
0
arrow_downward

Если вы хотите LEFT JOIN на Products, вам нужно включить это предложение в ON, а не в WHERE. Помещение его в WHERE делает его INNER JOIN.

ALTER PROCEDURE [dbo].[TasksByStatus]
  @Status  INT = NULL,
  @Project INT = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT 
    Task, 
    CONVERT(VARCHAR, StartDate, 101) AS StartDate, 
    (CASE WHEN CompleteDate IS NULL THEN 'Not complete yet' 
     ELSE CONVERT(VARCHAR, CompleteDate, 101) END) AS CompleteDate, 
    (CASE WHEN Notes IS NULL THEN 'No notes provided' ELSE Notes END) AS Notes, 
    ProjectName, StatusName
  FROM dbo.Tasks 
  INNER JOIN dbo.Status ON Tasks.StatusID = Status.ID
  LEFT JOIN dbo.Projects ON Tasks.ProjectID = Projects.ID 
  WHERE Status.ID = COALESCE(@Status, Status.ID) 
    AND COALESCE(Projects.ID, -1) = COALESCE(@Project, Projects.ID, -1)
  ORDER BY StartDate, StatusName;
END
GO
person Aaron Bertrand    schedule 08.03.2012
comment
Спасибо. На самом деле это показывает эти поля, но передача NULL в @Project также возвращает все остальные значения, а не только те, у которых есть значения ProjectID NULL. Есть ли способ сделать это, по-прежнему позволяя этому параметру быть необязательным, например, без передачи ему значения, а не NULL? - person Zogglet; 08.03.2012
comment
Вам нужно будет показать образцы данных и желаемые результаты с параметром NULL и без него. У меня проблемы со словом проблема, извините. - person Aaron Bertrand; 08.03.2012
comment
С параметром NULL: вернуть запись с Task, StartDate, CompleteDate, Notes, ProjectName и StatusName, только если поле ProjectID этой записи равно null. Без параметра NULL: показать все записи либо с указанным параметром @Status, либо, если он не указан, показать все записи. - person Zogglet; 08.03.2012
comment
Попробуйте сейчас, предполагая, что Projects.ID всегда положительный. Если это по-прежнему не дает желаемых результатов, ПОЖАЛУЙСТА, добавьте к вопросу образцы данных и желаемые результаты. - person Aaron Bertrand; 08.03.2012
comment
Я прошу прощения. Как бы вы хотели, чтобы я предоставил образцы данных и результаты в контексте этих комментариев? - person Zogglet; 08.03.2012
comment
Пожалуйста, добавьте их к вопросу, чтобы все знали, какие данные у вас есть и какие данные вы ожидаете получить. - person Aaron Bertrand; 08.03.2012

arrow_upward
0
arrow_downward

Этот вид запроса — кратчайший путь к катастрофе. Помните, что SQL должен составить план запроса, который работает для любого значения параметров. Поэтому он будет вынужден выполнять сканирование обеих таблиц Status и Projects, даже если были указаны @Project и/или @Status! Эрланд Соммарског подробно освещает эту тему в разделе Динамические условия поиска в T-SQL.

Разделите это на 3 разных запроса:

if (@Status is null and @Project is null)
   select ...;
else if (@status is null)
   select ... where ProjectID = @Project;
else if (@project is null)
   select ... where StatusID = @status;
else
   select ... where StatusID = @status and ProjectID = @Project;
person Remus Rusanu    schedule 08.03.2012
comment
Это ужасный, ужасный код для поддержки. Если размер таблицы становится проблемой, я бы предпочел обойти оптимизатор, используя динамический SQL и полагаясь на специальные планы. - person Aaron Bertrand; 08.03.2012
comment
@Aaron: Да, возможно, динамический SQL лучше, чем поддержка 4 форм запроса, хотя можно утверждать, что поддержка генерирующего кода динамического SQL может быть более сложной для устранения неполадок. Моя главная мысль заключается в том, что этот вид универсального запроса является катастрофой в процессе становления. В конечном счете, проблема заключается в дизайне приложения, который решил создать один API, который, по-видимому, выполняет 4 отдельные задачи, вместо того, чтобы создавать 4 разных API. Это просто плохой дизайн API, но я давно сдался, пытаясь решить это на форумах... - person Remus Rusanu; 08.03.2012