以动态/编程方式将 WHERE 子句添加到 SQL

本文介绍了以动态/编程方式将 WHERE 子句添加到 SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 如何以编程方式向 SQL 存储过程添加搜索条件?在我的应用程序(C#)中，我使用的是存储过程(SQL Server 2008R2)ALTER PROCEDURE [dbo].[PROC001]@userID varchar(20),@password varchar(20)作为SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password我想通过更多条件扩展这个查询，现在我不知道有多少条件会在程序执行时使用这个查询.. 2, 3, 6 OR 20.我想以编程方式添加这些条件，例如:SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @passwordAND 字段 2 = '1' AND 字段 3 = '0' OR 字段 4 '8' AND Field5 是否可以动态地向存储过程发送条件? 解决方案 编辑 - 首选基于 LINQ 的 ORM，如果可能如果您不需要在 ADO 中执行此操作，更好的解决方案是使用 ORM，它最终将构建参数化的 ad-hoc sql.这是两全其美的 - 您可以获得动态查询的灵活性，没有多余的过滤器来扰乱优化器，查询计划本身是可缓存的，并且您可以免受注入攻击之类的麻烦.一个基于 Linq 的 ORM 查询使阅读更容易://构建一个非物化的 IQueryable<>var usersQuery = db.Users;if (!string.IsNullOrEmpty(userID)){usersQuery = usersQuery.Where(u => u.Name == userId);}//当然，您不会梦想以明文形式存储密码.if (!string.IsNullOrEmpty(anotherField)){usersQuery = usersQuery.Where(u => u.AnotherColumn == anotherField);}...//具体化(并执行)查询varfilteredUsers = usersQuery.ToList();对于复杂的查询，您可能需要查看 PredicateBuilderADO/手动查询构建您可以使用 sp_executesql 动态构建 SQL，如下所示.如果您对变量进行参数化，那么您应该可以避免 SQL 注入和转义引号等问题，我们将为您处理.创建程序 [dbo].[PROC001]@userID varchar(20),@pwdHash varchar(20),@optionalParam1 NVARCHAR(50) = NULL -- 其他可选参数作为开始设置无计数声明 @SQL NVARCHAR(MAX)-- 此处查询的强制/静态部分.-- 明文密码被禁止，过滤器中的 RTRIM 是多余的SET @SQL = N'SELECT * FROM tUsers WHERE Name = @userID AND PwdHash = @pwdHash'如果@OptionalParam1 不为空开始SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1'结尾EXEC sp_executesql @SQL,N'@userID varchar(20),@pwdHash varchar(20),@optionalParam1 NVARCHAR(50)',@userID = @userID,@pwdHash = @pwdHash,@optionalParam1 = @optionalParam1结尾重新，为什么 WHERE (@x IS NULL OR @x = Column) 是个坏主意?(来自我下面的评论)尽管可选参数"模式作为瑞士军刀"在小表上使用时可以很好地查询可选过滤器的多种排列，但不幸的是，对于大表，这会导致所有排列的单一查询计划查询的过滤器，由于 参数嗅探问题.如果可能，您应该完全消除多余的过滤器.回复:为什么在谓词中应用函数是个坏主意例如WHERE SomeFunction(Column) = @someParameter在谓词中使用函数经常会取消 RDBMS 使用索引的资格("non-sargable").在这种情况下，RTRIM 不需要作为 Sql 服务器 在比较期间忽略尾随空格.>How can I add search condition to SQL Stored Procedure programmatically?In my application(C#) I'm using stored procedure (SQL Server 2008R2)ALTER PROCEDURE [dbo].[PROC001]@userID varchar(20),@password varchar(20)ASSELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @passwordI want to extend this query by more conditions, and now I don't know how many conditions will use this query due program execution.. 2, 3, 6 OR 20. I want to add these conditions programmatically like:SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @passwordAND Field2 = '1' AND Field3 = '0' OR Field4 <> '8' AND Field5 < '100' ....Is it possible to sent conditions to stored procedure dynamically? 解决方案 Edit - Preference for LINQ based ORM's, if possibleIf you don't need to do this in ADO, a better solution is to use an ORM which will ultimately build parameterized ad-hoc sql. This is the best of both worlds - you get the flexibility of a dynamic query, with no redundant filters to upset the optimizer, the query plan itself is cacheable, and you are safe from nasties like injection attacks. And a Linq-based ORM query makes for easy reading: // Build up a non-materialized IQueryable<> var usersQuery = db.Users; if (!string.IsNullOrEmpty(userID)) { usersQuery = usersQuery.Where(u => u.Name == userId); } // Of course, you wouldn't dream of storing passwords in cleartext. if (!string.IsNullOrEmpty(anotherField)) { usersQuery = usersQuery.Where(u => u.AnotherColumn == anotherField); } ... // Materialize (and execute) the query var filteredUsers = usersQuery.ToList();For complex queries, you may want to look at PredicateBuilderADO / manual query buildingYou can use sp_executesql to build up SQL dynamically as per below. Provided that you parameterize the variables you should be safe from issues like SQL injection and escaping quotes etc will be handled for you.CREATE PROCEDURE [dbo].[PROC001] @userID varchar(20), @pwdHash varchar(20), @optionalParam1 NVARCHAR(50) = NULL -- Other optional parametersAS BEGIN SET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) -- Mandatory / Static part of the Query here. -- Cleartext passwords are verboten, and RTRIM is redundant in filters SET @SQL = N'SELECT * FROM tUsers WHERE Name = @userID AND PwdHash = @pwdHash' IF @OptionalParam1 IS NOT NULL BEGIN SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1' END EXEC sp_executesql @SQL, N'@userID varchar(20), @pwdHash varchar(20), @optionalParam1 NVARCHAR(50)' ,@userID = @userID ,@pwdHash = @pwdHash ,@optionalParam1 = @optionalParam1 ENDRe, why is WHERE (@x IS NULL OR @x = Column) a bad idea?(From my comment below)Although the 'optional parameter' pattern works well as a 'swiss army knife' for querying a multitude of permutations of optional filters when used on small tables, unfortunately, for large tables, this results in a single query plan for all permutations of filters for the query, which can result in poor query performance with certain permutations of optional parameters due to the parameter sniffing problem. If possible, you should eliminate redundant filters entirely.Re: Why is applying functions in predicates a bad ideae.g.WHERE SomeFunction(Column) = @someParameterUse of functions in predicates frequently disqualifies the use of indexes by the RDBMS ("non-sargable").In this instance, RTRIM is unnecessary as Sql server ignores trailing spaces during comparison. 这篇关于以动态/编程方式将 WHERE 子句添加到 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！