添加WHERE子句到SQL动态/编程 [英] Add WHERE clauses to SQL dynamically / programmatically

问题描述

我如何添加搜索条件到SQL编程存储过程？ 在我的应用程序（C＃）我使用存储过程（SQL服务器2008 R2）

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)
AS
SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password

我想通过更多的条件来扩展此查询，现在我不知道有多少条件将使用此查询由于程序执行。2，3，6或20。我希望以编程方式添加这些条件，如：

I 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) = @password
AND Field2 = '1' AND Field3 = '0' OR Field4 <> '8' AND Field5 < '100' ....

是否有可能发送条件存储过程动态？

Is it possible to sent conditions to stored procedure dynamically?

推荐答案

您可以使用 sp_executesql的来动态地建立SQL按如下。前提是你参数，你应该是安全的，如SQL注入的问题变量和转义引号等会为您处理。

You 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.

不过，您可能还需要考虑使用像实体框架或LINQ2SQL的ORM从您的应用程序动态建立查询。这将节省编码你很多。

However, you might also want to consider using an ORM like Entity Framework or LINQ2SQL to build up queries dynamically from your application. It will save you a lot of coding.

CREATE PROCEDURE [dbo].[PROC001]
    @userID varchar(20),
    @password varchar(20),
    @optionalParam1 NVARCHAR(50) = NULL -- Other optional parameters
AS        
    BEGIN        
        SET NOCOUNT ON        

        DECLARE @SQL NVARCHAR(MAX)        

        -- Mandatory / Static part of the Query here
        SET @SQL = N'SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password'

        IF @OptionalParam1 IS NOT NULL        
            BEGIN        
                SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1'    
            END        

        EXEC sp_executesql @SQL,        
            N'@userID varchar(20),
            @password varchar(20),
            @optionalParam1 NVARCHAR(50)'
            ,@userID = @userID
            ,@password = @password
            ,@optionalParam1 = @optionalParam1
    END

这篇关于添加WHERE子句到SQL动态/编程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！