[SQL] 선택 "WHERE"매개 변수를 사용하여 저장 프로 시저

선택 "WHERE"매개 변수를 사용하여 저장 프로 시저

나는 사용자가 일부 데이터 (상태 등 날짜)를 통해 발굴하기 위해 다양한 매개 변수를 지정할 수있는 형태를 가지고있다.

I는 쿼리를 생성 할 수 있습니다 :

SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>

등 선택적 WHERE 각 (I 상태 = 3 또는 날짜 = 1980년 10월 10일 또는 모든 = 3 및 날짜 = 1980년 10월 10일 등 상태의 행을 가진 모든 행과 모든 행을 선택할 수있다) .

모든 옵션 매개 변수의 많은 감안할 때, 동적 저장 프로 시저를 구성하는 가장 좋은 방법은 무엇입니까?

나는 같은 다양한 DB에 일하고 있어요 : MySQL은, 오라클, SQLServer에.

해결법

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

   1.이 작업을 수행하는 가장 쉬운 방법 중 하나 :

   이 작업을 수행하는 가장 쉬운 방법 중 하나 :

   SELECT * FROM table 
   WHERE ((@status_id is null) or (status_id = @status_id))
   and ((@date is null) or ([date] = @date))
   and ((@other_parameter is null) or (other_parameter = @other_parameter))
   

   기타 이것은 완전히 동적 SQL을 제거하고 하나 개 이상의 필드에 검색 할 수 있습니다. 동적 SQL을 제거함으로써 아직 SQL 주입에 대한 또 다른 보안 문제를 제거합니다.

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

   2.이 같은 프로 시저를 만듭니다 :

   이 같은 프로 시저를 만듭니다 :

   CREATE PROCEDURE [dbo].[spXXX]
       @fromDate datetime = null,
       @toDate datetime = null,
       @subCode int = null
   as
   begin
   set NOCOUNT ON
   /* NOCOUNT limits the server feedback on select results record count */
   SELECT
       fields...
   FROM
       source
   WHERE
       1=1
   --Dynamic where clause for various parameters which may or may not be passed in.
   and ( @fromDate is null or [dateField] >= @fromDate)
   and ( @toDate is null or [dateField] <= @toDate)
   and ( @subCode is null or subCode= @leaveTypeSubCode)
   order by fields...
   

   이것은 당신이 0 PARAMS 모든 PARAMS, 또는 PARAMS의 #으로 프로 시저를 실행 할 수 있습니다.

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

   3.이 스타일 I의 사용이다 :

   이 스타일 I의 사용이다 :

   T-SQL

   SELECT    *        
   FROM    table        
   WHERE     
   status_id    =    isnull(@status_id ,status_id)     
   and    date    =    isnull(@date ,date )     
   and    other_parameter    =    isnull(@other_parameter,other_parameter) 
   

   신탁

   SELECT    *        
   FROM    table        
   WHERE     
   status_id    =    nval(p_status_id ,status_id)     
   and    date    =    nval(p_date ,date )     
   and    other_parameter    =    nval(p_other_parameter,other_parameter)
   

4. ==============================

   4.읽기 가능한 및 유지 관리 방법은 (/ 가입으로도 사용할 적용)을 수행하는 :

   읽기 가능한 및 유지 관리 방법은 (/ 가입으로도 사용할 적용)을 수행하는 :

   where 
         (@parameter1 IS NULL OR your_condition1)
     and (@parameter2 IS NULL OR your_condition2) 
   -- etc
   

   당신의 실행 계획이 NULL 값을 무시하고 (: NULL 값에 대한 모든 테이블 검색을 scaning 예) 대규모 성능 허점을 생성하지 않습니다 때문에 그것은, (더 / 가입 적용하여) 가장 큰 테이블에 대한 나쁜 생각입니다.

   SQL Server의 로터리 방법은 쿼리에 함께 (RECOMPILE) 옵션을 사용하는 것입니다 (사용 가능한부터 SQL 2008 SP1의 CU5 (10.0.2746)).

   구현하는 가장 좋은 방법이 (현명한 성능) 가능한 조합마다 ELSE IF ... 블록을 사용하는 것이다. 아마 소진되어 있지만 최고의 공연을하고 당신의 데이터베이스 설정을 중요하지 않습니다.

   당신이 더 많은 정보가 필요한 경우, KM 찾아보실 수 있습니다. 여기에 답합니다.

5. ==============================

   5.당신처럼 뭔가를 할 수

   당신처럼 뭔가를 할 수

   WHERE 
   (
    ParameterA == 4 OR ParameterA IS NULL
   )
   
   AND
   (
    ParameterB == 12 OR ParameterB IS NULL
   )
   

6. ==============================

   6.당신이 (보통 피해야하는) SQL 문자열을 구축 동적으로 피하고 싶은 경우에, 당신은 동일합니다 기본값으로 claused 여기서 "무시"당신의 각 critera을 비교하여 저장 발동에이 작업을 수행 할 수 있습니다. 예컨대 :

   당신이 (보통 피해야하는) SQL 문자열을 구축 동적으로 피하고 싶은 경우에, 당신은 동일합니다 기본값으로 claused 여기서 "무시"당신의 각 critera을 비교하여 저장 발동에이 작업을 수행 할 수 있습니다. 예컨대 :

   select * from Table where
      (@Col1 IS NULL OR Col1 = @Col1) /*If you don't want to filter in @col, pass in NULL*/
      AND
      (@Col2 IS NULL OR Col2 = @Col2)
   

from https://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters by cc-by-sa and MIT license