[SQL] 프로그래밍 / 동적 SQL에 WHERE 절을 추가

프로그래밍 / 동적 SQL에 WHERE 절을 추가

어떻게 프로그래밍 SQL 저장 프로 시저에 검색 조건을 추가 할 수 있습니까? 내 응용 프로그램 (C #을) 나는 저장 프로 시저를 사용하고 있습니다 (SQL 서버 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)로 인해이 쿼리를 사용하는 방법을 모른다 :

SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password
AND Field2 = '1' AND Field3 = '0' OR Field4 <> '8' AND Field5 < '100' ....

이 저장 프로 시저 동적으로 전송 조건에 수 있습니까?

해결법

1. ==============================

   1.편집 - 기반의 LINQ위한 특혜 ORM의, 가능하면

   편집 - 기반의 LINQ위한 특혜 ORM의, 가능하면

   당신이 ADO에서이 작업을 수행 할 필요가없는 경우, 더 나은 솔루션은 궁극적으로 매개 변수가있는 임시 SQL을 구축 할 것 ORM을 사용하는 것입니다. 이 두 세계의 최고 - 당신은 최적화 자체가 캐시 쿼리 계획을 화나게없이 중복 필터, 동적 쿼리의 유연성을 얻을, 당신은 주입 공격 등의 악성 콘텐츠로부터 안전합니다. 그리고 Linq에 기반 ORM 쿼리는 쉽게 읽을 수있게 :

    // 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();
   

   복잡한 쿼리의 경우, PredicateBuilder보고 할 수 있습니다

   ADO / 수동 쿼리 작성

   아래에 따라 동적 SQL을 구축하는 sp_executesql을 사용할 수 있습니다. 당신은 당신이 당신을 위해 처리되는 등 SQL 주입 및 탈출 따옴표 같은 문제로부터 안전해야 변수를 파라미터 것을 제공.

   CREATE PROCEDURE [dbo].[PROC001]
       @userID varchar(20),
       @pwdHash 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. 
           -- 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
       END
   

   나쁜 생각 (@x는 NULL 또는 @x = 열이 IS) WHERE 다시는, 왜?

   (아래 내 댓글에서)

   '선택적 매개 변수'패턴은 작은 테이블에 사용할 경우 옵션 필터의 순열의 수많은 질의를위한 '스위스 군용 칼'으로 잘 작동하지만 불행하게도, 큰 테이블, 필터의 모든 순열에 대한 단일 쿼리 계획에서이 결과 때문에 매개 변수 스니핑 문제에 선택적 매개 변수의 특정 순열과 가난한 쿼리 성능이 저하 될 수있는 쿼리. 가능하다면, 당신은 완전히 중복 된 필터를 제거해야합니다.

   제목 : Re : 왜 나쁜 생각 술어에 기능을 적용

   EG

   WHERE SomeFunction(Column) = @someParameter
   

   술어 함수의 사용은 자주 RDBMS ( "비 스 SARGable")에 의해 인덱스의 사용 자격을 상실.

   이때, RTRIM은 SQL 서버 무시 비교시 후행 공백로서 불필요하다.

2. ==============================

   2.이 같은 SQL에만이 작업을 수행 할 수 있습니다 :

   이 같은 SQL에만이 작업을 수행 할 수 있습니다 :

   SELECT * 
   FROM tUsers 
   WHERE 1 = 1
     AND (@userID IS NULL OR RTRIM(Name) = @userID )
     AND (@password IS NULL OR RTRIM(Password) = @password)
     AND (@field2 IS NULL OR Field2 = @field2)
   ....
   

   어떤 매개 변수가 NULL 값을 저장 프로 시저에 전달 된 경우, 전체 조건은 무시됩니다.

   참고 것을 : 1 = 1이 항상 참이기 때문에 내가 추가 한 경우 쿼리와 결과 집합 alll이 경우 전달에는 매개 변수를 쿼리 작업을하지 않습니다하기 위해 1 = 1이 반환됩니다 WHERE.

3. ==============================

   3.당신은 문자열로 프로 시저를 가지고 있고, 조건, CONCATENATE 및 간부로 문자열을 보낼 수 있습니다.

   당신은 문자열로 프로 시저를 가지고 있고, 조건, CONCATENATE 및 간부로 문자열을 보낼 수 있습니다.

   ALTER PROCEDURE [dbo].[PROC001] @userID varchar(20), @password varchar(20), @WhereToAdd varchar(MAX) AS 
   
   exec ('SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password AND ' + @WhereToAdd)
   

from https://stackoverflow.com/questions/11329823/add-where-clauses-to-sql-dynamically-programmatically by cc-by-sa and MIT license