옵션인 "WHERE" 매개 변수가 있는 저장 프로시저

옵션인 "WHERE" 매개 변수가 있는 저장 프로시저

사용자가 다양한 파라미터를 지정하여 데이터(상태, 날짜 등)를 검색할 수 있는 폼이 있습니다.

다음과 같은 쿼리를 생성할 수 있습니다.

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

등WHERE옵션입니다(모든 행을 선택할 수 있습니다).status = 3, 또는 모든 행에 대해date = 10/10/1980, 또는 모든 행에 대해status = 3 AND date = 10/10/1980등)을 클릭합니다.

많은 수의 매개 변수가 모두 옵션인 경우 동적 저장 프로시저를 구성하는 가장 좋은 방법은 무엇입니까?

MySQL, Oracle, SQL Server 등 다양한 DB 작업을 하고 있습니다.

이를 실현하기 위한 가장 쉬운 방법 중 하나는 다음과 같습니다.

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 주입과 관련된 또 다른 보안 문제를 제거할 수 있습니다.

다음과 같이 절차를 작성합니다.

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개의 파라미터, 모든 파라미터 또는 임의의 수의 파라미터를 사용하여 프로시저를 실행할 수 있습니다.

사용하는 스타일은 다음과 같습니다.

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)

읽기 쉽고 유지보수 가능한 방법(JOIN/APPLY에서 사용 가능):

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

그러나 대부분의 큰 테이블(JOIN/APPLY 사용 시)에서는 실행 계획이 NULL 값을 무시하지 않고 엄청난 성능(예: NULL 값을 검색하는 모든 테이블의 허점)을 발생시키기 때문에 이는 좋지 않습니다.

SQL Server에서 우회적으로 사용하는 방법은 쿼리에서 WITH(RECOMPILE) 옵션을 사용하는 것입니다(SQL 2008 SP1 CU5(10.0.2746) 이후 사용 가능).

이것을 실장하는 최선의 방법(퍼포먼스에 최적)은, IF...를 사용하는 것입니다.가능한 각 조합에 대해 하나씩, ELSE 블록.피곤할 수도 있지만 최고의 성능을 발휘할 수 있으며 데이터베이스 설정은 중요하지 않습니다.

자세한 내용은 KM에서 확인하실 수 있습니다.여기서 답변해 주세요.

뭐 이런 거 할 수 있어요

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

SQL 문자열이 동적으로 구축되는 것을 방지하려면(대부분 피하는 것이 가장 좋습니다), 저장된 프로세서에서 각 크리티컬을 기본값(무시)과 비교하여 이 작업을 수행할 수 있습니다.예:

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)

언급URL : https://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters