如何在存储过程中的同一列上使用两个参数

我试图在堆栈溢出中找到一个类似的问题,但没有成功...

这是我的存储过程代码:

USE [DATABASENAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SPNAME]
  @IN_dtmin datetime, 
  @IN_dtmax datetime = null, 
  @IN_key varchar(500), 
  @IN_set varchar(80), 
  @IN_locktype varchar(500)
WITH EXEC AS CALLER
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT IDLOCK, DT, LOCKTYPE,
       MAX(CASE WHEN PRODUCTATTRIBUTE = 'CodeSet' THEN  VALUE END) AS CodeSet,
       MAX(CASE WHEN PRODUCTATTRIBUTE = 'KeySet' THEN  VALUE END) AS KeySet
FROM LOCKREGISTER LR LEFT JOIN 
     LOCKTYPES T
     ON LR.IDLOCKTYPE = T.IDLOCKTYPE LEFT JOIN 
     PRODUCTATTRIBUTES PA
     ON LR.IDPRODUCT = PA.IDPRODUCT AND 
        PRODUCTATTRIBUTE IN ('CodeSet','KeySet')
WHERE LR.DT BETWEEN IIF(@IN_DTMIN IS NULL,GETDATE(),@IN_DTMIN) AND IIF(@IN_DTMAX IS NULL,GETDATE(),@IN_DTMAX)
  AND (PA.VALUE like ISNULL(@IN_key,'%') OR (PA.VALUE IS NULL AND @IN_key IS NULL)) 
  AND (PA.VALUE like ISNULL(@IN_set,'%') OR (PA.VALUE IS NULL AND @IN_set IS NULL))
  AND (T.LOCKTYPE like ISNULL(@IN_locktype,'%') OR (T.LOCKTYPE IS NULL AND @IN_locktype IS NULL))
GROUP BY IDLOCK, DT, LOCKTYPE;
END

如果我不应用任何过滤器,则结果如下:

enter image description here

And this is the result if I execute the Stored Procedure with the dates filter and the Set filter:

enter image description here

如上图所示,在CodeSet列中,结果变为“ NULL”。

执行SP时如何保留CodeSet列的值? (在某些情况下,某些过滤器可能为null)。

这将是第一行预期结果的示例:

enter image description here

评论
  • mut
    mut 回复

    我从您的代码中了解到的是,您只需要一个ISNULL函数。因此,将您的SELECT查询升级到-

    SELECT IDLOCK, DT, LOCKTYPE,
           ISNULL(MAX(CASE WHEN PRODUCTATTRIBUTE = 'CodeSet' THEN  VALUE END), @IN_set) AS CodeSet,
           ISNULL(MAX(CASE WHEN PRODUCTATTRIBUTE = 'KeySet' THEN  VALUE END), @IN_set) AS KeySet
    FROM LOCKREGISTER LR LEFT JOIN 
         LOCKTYPES T
         ON LR.IDLOCKTYPE = T.IDLOCKTYPE LEFT JOIN 
         PRODUCTATTRIBUTES PA
         ON LR.IDPRODUCT = PA.IDPRODUCT AND 
            PRODUCTATTRIBUTE IN ('CodeSet','KeySet')
    WHERE LR.DT BETWEEN IIF(@IN_DTMIN IS NULL,GETDATE(),@IN_DTMIN) AND IIF(@IN_DTMAX IS NULL,GETDATE(),@IN_DTMAX)
      AND (PA.VALUE like ISNULL(@IN_key,'%') OR (PA.VALUE IS NULL AND @IN_key IS NULL)) 
      AND (PA.VALUE like ISNULL(@IN_set,'%') OR (PA.VALUE IS NULL AND @IN_set IS NULL))
      AND (T.LOCKTYPE like ISNULL(@IN_locktype,'%') OR (T.LOCKTYPE IS NULL AND @IN_locktype IS NULL))
    GROUP BY IDLOCK, DT, LOCKTYPE;
    
  • cvitae
    cvitae 回复

    I believe the problem is the fact that PRODUCTATTRIBUTES is an Entity Attribute Value (EAV) table, which are notoriously hard to deal with in queries.

    Note that in the second execution of the query, which includes the parameters, the value you entered for @IN_set shows up in the KeySet column because you're not distinguishing between PRODUCTATTRIBUTE values (CodeSet vs KeySet) in your WHERE clause.

    In order to simplify the logic in queries like this, I typically join to the EAV table repeatedly, creating sub-queries that only contain the key/value pairs I need for each attribute. So, in this case, I would join to PRODUCTATTRIBUTES twice, once to retrieve the CodeSet values of interest, then again to get the KeySet values. I would also shift the aggregation to those sub-queries, which eliminates ambiguity and also reduces the amount of data being pulled into memory.

    当然,这是未经测试的,因为缺少要测试的表和数据,但是对新结构的有根据的猜测看起来像这样。

    SELECT IDLOCK, DT, LOCKTYPE,
           CS.CodeSet,
           KS.KeySet
    FROM LOCKREGISTER LR LEFT JOIN 
         LOCKTYPES T
         ON LR.IDLOCKTYPE = T.IDLOCKTYPE LEFT JOIN 
         (
          SELECT 
            IDPRODUCT,
            MAX(VALUE) AS CodeSet
          FROM PRODUCTATTRIBUTES
          WHERE PRODUCTATTRIBUTE = 'CodeSet'
          GROUP BY IDPRODUCT
         ) AS CS
         ON LR.IDPRODUCT = CS.IDPRODUCT LEFT JOIN
         (
          SELECT 
            IDPRODUCT,
            MAX(VALUE) AS KeySet
          FROM PRODUCTATTRIBUTES
          WHERE PRODUCTATTRIBUTE = 'KeySet'
          GROUP BY IDPRODUCT
         ) AS KS
         ON LR.IDPRODUCT = KS.IDPRODUCT
    
    WHERE LR.DT BETWEEN IIF(@IN_DTMIN IS NULL,GETDATE(),@IN_DTMIN) AND IIF(@IN_DTMAX IS NULL,GETDATE(),@IN_DTMAX)
      AND (KS.KeySet like ISNULL(@IN_key,'%') OR (KS.KeySet IS NULL AND @IN_key IS NULL)) 
      AND (CS.CodeSet like ISNULL(@IN_set,'%') OR (CS.CodeSet IS NULL AND @IN_set IS NULL))
      AND (T.LOCKTYPE like ISNULL(@IN_locktype,'%') OR (T.LOCKTYPE IS NULL AND @IN_locktype IS NULL));