使用变量过滤多个列

I have an SQL table that looks like this:

我有一个如下所示的SQL表：

DECLARE @BI_ResponsibleID INT , @CategoryID  INT , @ChangeRequestorID INT 

SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]

I would like to be able to filter the columns depending on my variables value(s)

我希望能够根据我的变量值过滤列

E.G sometimes only @BI_ResponsibleID other times @CategoryID and @ChangeRequestorID and so on.....

E.G有时只有@BI_ResponsibleID其他时候@CategoryID和@ChangeRequestorID等等.....

I this possible with my variables?

我可以使用我的变量吗？

6 个解决方案

#1

You can use dynamic query something below. I'm assuming if the variable's values is zero you don't want to filter on that column.

您可以使用下面的动态查询。我假设如果变量的值为零，您不想在该列上进行过滤。

// Not tested code
DECLARE @BI_ResponsibleID INT , @CategoryID  INT , @ChangeRequestorID INT 
DECLARE @sqlCommand nvarchar(1000)


SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SET @sqlCommand = 'TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity] where 1 = 1 '

if(@BI_ResponsibleID != 0)
 set @sqlCommand +=  'AND BI_ResponsibleID = @BI_ResponsibleID '

 if(@CategoryID != 0)
 set @sqlCommand +=  'AND CategoryID = @CategoryID ' 

 if(@ChangeRequestorID != 0)
 set @sqlCommand +=  'AND ChangeRequestorID = @ChangeRequestorID '

EXEC sp_executesql @sqlCommand, N'@BI_ResponsibleID INT, @CategoryID INT, @ChangeRequestorID INT', @BI_ResponsibleID, @CategoryID, @ChangeRequestorID;

#2

Try something like this:

尝试这样的事情：

DECLARE @BI_ResponsibleID INT , @CategoryID INT , @ChangeRequestorID INT

DECLARE @BI_ResponsibleID INT，@ CategoryID INT，@ ChangeRequestorID INT

SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SELECT TOP 4
     [BI_ResponsibleID],
     [CategoryID],
     [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE (ISNULL([BI_ResponsibleID],0) = @BI_ResponsibleID) 
    AND (ISNULL([CategoryID],0) = @CategoryID)
    AND (ISNULL([ChangeRequestorID],0) = @ChangeRequestorID)

#3

My solution for you:

我的解决方案：

Using default value for null parameter like this

像这样使用null参数的默认值

SET @BI_ResponsibleID = ISNULL(@BI_ResponsibleID, 0 );
SET @CategoryID = ISNULL(@CategoryID, 0 );
SET @ChangeRequestorID = ISNULL(@ChangeRequestorID, 0 );

And try this:

试试这个：

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE (@BI_ResponsibleID = 0 OR [BI_ResponsibleID] = @BI_ResponsibleID)
  AND (@CategoryID = 0 OR [CategoryID] = @CategoryID)
  AND (@ChangeRequestorID = 0 OR [ChangeRequestorID] = @ChangeRequestorID)

#4

Because I do not have tables and data, I cannot check this query. But this is how you can achieve it.

因为我没有表和数据，所以无法检查此查询。但这就是你如何实现它。

DECLARE @BI_ResponsibleID INT , @CategoryID  INT , @ChangeRequestorID INT 

SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE ( 1 = 1 OR ( @BI_ResponsibleID IS NOT NULL AND  Some Other condition using @BI_ResponsibleID))
    AND ( 1 = 1 OR ( @CategoryID IS NOT NULL AND  Some Other condition using @CategoryID))
    AND ( 1 = 1 OR ( @ChangeRequestorID IS NOT NULL AND  Some Other condition using @ChangeRequestorID))

#5

DECLARE @BI_ResponsibleID INT , @CategoryID  INT , @ChangeRequestorID INT 

SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE ( BI_ResponsibleID = @BI_ResponsibleID OR @BI_ResponsibleID IS NULL )
      AND ( CategoryID = @CategoryID OR @CategoryID IS NULL )
      AND ( ChangeRequestorID = @ChangeRequestorID OR @ChangeRequestorID IS NULL )

#6

What you need to do is, include OR condition to check NULL parameters like this:

你需要做的是，包括OR条件来检查NULL参数，如下所示：

DECLARE @BI_ResponsibleID INT , @CategoryID  INT , @ChangeRequestorID INT 

SET @BI_ResponsibleID = 5
SET @CategoryID = 3
SET @ChangeRequestorID = 4

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE 
    (BI_ResponsibleID = @BI_ResponsibleID OR @BI_ResponsibleID IS NULL)
    AND (CategoryID = @CategoryID OR @CategoryID IS NULL)
    AND (ChangeRequestorID = @ChangeRequestorID OR @ChangeRequestorID IS NULL)

You may want to do this via stored proc like this:

您可能希望通过存储过程执行此操作，如下所示：

CREATE PROCEDURE MyProcName
    @BI_ResponsibleID = NULL
    @CategoryID = NULL
    @ChangeRequestorID = NULL

AS

SELECT TOP 4
      [BI_ResponsibleID],
      [CategoryID],
      [ChangeRequestorID]   
FROM [BI_Planning].[dbo].[tlbActivity]
WHERE 
    (BI_ResponsibleID = @BI_ResponsibleID OR @BI_ResponsibleID IS NULL)
    AND (CategoryID = @CategoryID OR @CategoryID IS NULL)
    AND (ChangeRequestorID = @ChangeRequestorID OR @ChangeRequestorID IS NULL)

END

#1