当使用两列进行比较时的WHERE子句填充

huangapple go评论69阅读模式
英文:

where clause filing when two columns are used to compare

问题

在这里是您的翻译:

"the -- one is failing because if i use OR, it gets all record, at one of time, it will either be assignmentrecordid or activity_id, but cannot be both,
if i use and, it does not get any results what am i doing wrong here"

"双破折号 -- 失败是因为如果我使用 OR 运算符,它会获取所有记录,但在某一时刻,要么是 assignmentrecordid,要么是 activity_id,但不能同时是两者之一,如果我使用 AND 运算符,它不会返回任何结果,我在这里做错了什么?"

英文:

where condition failing if one of the conditions is specified and it does not seems to be wrong because i am specifying AND operator but i am not 100% sure on it and one point, only 1 value willbe active, either activity_id or assignmentrecordID

Here is my code

SELECT DISTINCT
              assignmentRecordId,
              activityId,
              activityType,
              activityTitle,
              activityDescription,
              
FROM dbo.Activity
WHERE ((@assignmentRecordId IS NULL OR assignmentRecordID = @assignmentRecordId) -- failing
  AND (@activity_id IS NULL OR activity_id = @activity_id)) -- this and he above is failing
  AND (@Search = '' OR (@Search != '' AND    (
                                    assignmentRecordId LIKE '%'+@Search+'%'
                                OR    activityNumber LIKE '%'+@Search+'%'
                                OR    activityTitle LIKE '%'+@Search+'%'
  )))

the -- one is failing because if i use OR, it gets all record, at one of time, it will either be assignmentrecordid or activity_id, but cannot be both,
if i use and, it does not get any results what am i doing wrong here

and everything described in where clause can be NULL

答案1

得分: -2

这种类型的查询被称为“厨房水槽查询”1

您应该为指定的参数构建并执行动态 SQL 查询。

DECLARE @sql nvarchar(max)  = '
SELECT
  assignmentRecordId,
  activityId,
  activityType,
  activityTitle,
  activityDescription            
FROM dbo.Activity a
WHERE 1=1
';

IF @assignmentRecordId IS NOT NULL
    SET @sql += '
  AND assignmentRecordID = @assignmentRecordId';

IF @activity_id IS NOT NULL
    SET @sql += '
  AND activity_id = @activity_id';

IF ISNULL(@Search, '') <> ''
    SET @sql += '
  AND (
       assignmentRecordId LIKE ''%'' + @Search + ''%''
    OR activityNumber     LIKE ''%'' + @Search + ''%''
    OR activityTitle      LIKE ''%'' + @Search + ''%''
  )';

PRINT @sql;  -- 您的朋友

EXEC sp_executesql @sql
  N'@assignmentRecordId int,
    @activity_id int,
    @Search nvarchar(1000)',
  @assignmentRecordId = @assignmentRecordId,
  @activity_id = @activity_id,
  @Search = @Search;

请注意,实际参数仍然通过而不是被注入。

根据要求,您可能希望使用ELSE IF @activity_id IS NOT NULL代替。

英文:

This type of query is called a Kitchen Sink Query.

You should build and execute a dynamic SQL query for the parameters specified.

DECLARE @sql nvarchar(max)  = '
SELECT
  assignmentRecordId,
  activityId,
  activityType,
  activityTitle,
  activityDescription            
FROM dbo.Activity a
WHERE 1=1
';

IF @assignmentRecordId IS NOT NULL
    SET @sql += '
  AND assignmentRecordID = @assignmentRecordId';

IF @activity_id IS NOT NULL
    SET @sql += '
  AND activity_id = @activity_id';

IF ISNULL(@Search, '') <> ''
    SET @sql += '
  AND (
       assignmentRecordId LIKE ''%'' + @Search + ''%''
    OR activityNumber     LIKE ''%'' + @Search + ''%''
    OR activityTitle      LIKE ''%'' + @Search + ''%''
  )';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql
  N'@assignmentRecordId int,
    @activity_id int,
    @Search nvarchar(1000)',
  @assignmentRecordId = @assignmentRecordId,
  @activity_id = @activity_id,
  @Search = @Search;

Note how the actual parameters are still passed through and not injected.

Depending on requirements you may want ELSE IF @activity_id IS NOT NULL instead

huangapple
  • 本文由 发表于 2023年6月19日 22:25:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76507558.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定