SQL Server where clause filing when two columns are used to compare

edqdpe6u 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(88)

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

sql-server

来源：https://stackoverflow.com/questions/76507558/where-clause-filing-when-two-columns-are-used-to-compare

1条答案

按热度按时间

mf98qq941#

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

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server where clause filing when two columns are used to compare

1条答案

相关问题

热门标签

最新问答