英文:
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 DISTINCTassignmentRecordId,activityId,activityType,activityTitle,activityDescription,FROM dbo.ActivityWHERE ((@assignmentRecordId IS NULL OR assignmentRecordID = @assignmentRecordId) -- failingAND (@activity_id IS NULL OR activity_id = @activity_id)) -- this and he above is failingAND (@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) = 'SELECTassignmentRecordId,activityId,activityType,activityTitle,activityDescriptionFROM dbo.Activity aWHERE 1=1';IF @assignmentRecordId IS NOT NULLSET @sql += 'AND assignmentRecordID = @assignmentRecordId';IF @activity_id IS NOT NULLSET @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 @sqlN'@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) = 'SELECTassignmentRecordId,activityId,activityType,activityTitle,activityDescriptionFROM dbo.Activity aWHERE 1=1';IF @assignmentRecordId IS NOT NULLSET @sql += 'AND assignmentRecordID = @assignmentRecordId';IF @activity_id IS NOT NULLSET @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 friendEXEC sp_executesql @sqlN'@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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论