英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论