출처1 : http://bluejini.co.kr/m/post/10
출처2 : https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008

2018/07/24 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 동적 프로시저 dynamic procedure

조회조건이 유동적인 프로시저호출

--정적 쿼리를 사용하는 프로시저에서
1. IF, OR과 case를 피하라

-부하가 많이 걸리는 예
( case @OrderID when '%' then @OrderID else OrderID end ) = @OrderID
(OrderID = @orderid OR @orderid IS NULL)

/*
sql server의 경우 where절에서 case 문을 사용할 때
then 부분에 bool 식을 바로 적을 수 없다. 
그래서 아래처럼 변경을 해줘야 하는데, 인덱스를 제대로 탄다는 보장이 없다.
권장하지는 않음
*/

declare @alarm_recovery varchar(1);
set @alarm_recovery = '1';

select *
from alarm_history as a
WHERE 1=1
    and (
            (ISNULL(@alarm_recovery, '') = '' AND 1 = 1)
            OR
            (@alarm_recovery = '0' AND a.alarm_recovery = '0')
            OR
            (@alarm_recovery > '0' AND a.alarm_recovery > '0')              
        )
order by alarm_recovery desc;

-권장되는예(NULL이 없어야함)
od.ProductID = coalesce(@prodid, od.ProductID)

2. coalesce 사용

-NULL이 존재하는 경우의 검색방법
1)컬림이 int형일 경우
DECLARE @minint int, @maxint int

SELECT @minint = convert(int, 0x80000000),
@maxint = convert(int, 0x7FFFFFFF)

OrderID BETWEEN coalesce(@orderid, @minint) AND
coalesce(@orderid, @maxint)

*NULL이 들어갈 수없음(int구조이기때문)

2) 컬림이 char(nchar)일 경우
DECLARE @mincustid nchar(10), @maxcustid nchar(10),

IF @custid IS NULL
SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
FROM Customers

CustomerID BETWEEN coalesce(@custid, @mincustid) AND
coalesce(@custid, @maxcustid)

*NULL이 존재하는 char(nchar)형식일 경우 최소/최대값을 이용하도록 한다

3) 컬럼이 varchar(nvarchar)일 경우 (OR쓸 수 밖에..)
(Region = @region OR @region IS NULL)

4) 날짜 컬럼일 경우
AND OrderDate >= coalesce(@fromdate, '17530101')
AND OrderDate <= coalesce(@todate, '99991231')

5) LIKE검색이 필요한 컬럼
ProductName LIKE coalesce(@prodname + '%', '%')

-- coalesce경우 다른(OR, CASE)처럼 만큼은 아니지만 scan을 피할 수없다.
따라서, 위의 방법으로도 성능이 나쁘다면 다이나믹 SQL(sp_executesql)을 사용하라

*다이나믹 SQL(sp_executesql)을 사용고려

-장점
OR, CASE와는 달리 scan을 최소한으로 줄이는 최적화된 index seek쿼리가 가능하므로
수행속도가 빠르다.

-단점(알려진)
1) 항상 재컴파일된다?..plan cash를 할 수없다?
정적프로시저와 다이나믹프로시저(sp_executesql)는 plan캐쉬가 다르게 작동한다.
정적프로시저는 매개변수(input)와는 상관없이 프로시저(objectid)기준으로 plan을 만들고 캐쉬한다
따라서, 첫번째 매개변수만 사용했을 경우, 두번째 매개변수만을 사용했을 경우 plan이 만들어져 있다면 캐쉬한다.

다이나믹프로시저(sp_executesql)는 statment기준으로 캐쉬한다

첫번째 매개변수만을 넣어 생성한 statment와 두번쨰 매개변수만을 넣어 생성한 statment는 다른 plan을 세우지만 해당 statment자체를 캐쉬하므로 다음부터 동일패턴이 들어오면 캐쉬한다.

따라서, 정적프로시저보다 매개변수 패턴만큼 plan을 세우므로 정적프로시저보다 나쁘다고 말할 수는 있지만, 상황에 따라 많은 Scan(논리적읽기가 커짐)현상으로 정적프로시저의 성능이 나빠진다면, 오히려 해당 statment에 최적화된 쿼리를 생성하는 다이나믹프로시저(sp_executesql)를 사용하는 것이 정확한 인덱스를 사용하면서 캐쉬할 수있다(재사용 가능)

이것은, ad-hoc쿼리와는 다른것이다(해당 쿼리는 정확히 일치하지 않으면 다 다른 것으로 간주되지만 다이나믹프로시저(sp_executesql)는 parameter방식이기 때문에 성능이 좋다)

2) 보안설정을 할 수없다
정적프로시저는 해당 프로시저에 소유자 기반의 보안(Role)을 줄 수있지만, 다이나믹프로시저는 그럴수없다.
이것은 조회조건을 제외한 나머지 메인쿼리부분을 view나 function을 사용하여 Role을 주고
이것을 다이니믹프로시저에서 select하여 조회조건부분을 적용하면 해결할 수있다
(단지, view(function)을 하나 더 만들어야하는 단점이 있지만, 실제로 프로시저에 role을 주는 곳은
많지 않고(금융권), 모든 프로시저를 다이나믹프로시저(수행이 나쁜 것만 선별적 테스트하여 적용)로 사용하지 않기때문이다

3) 유지보수가 힘들다
그렇다고 볼 수있다. 하지만, 모든 프로시저를 다이나믹프로시저(sp_executesql)를 사용하지 않고
문제가 된는 프로시저와 비교테스트하여 사용한다면 좋은 결과를 얻을 수있는데도 불구하고, 유지보수가 힘들다라고 아예 배제하는 것은 더욱 좋지않다

4) SQL Injection의 위험이 있다
parameter방식으로 프로시저에서 사용하며, sp_executesql는 내부적으로 별도의 parameter를 더 사용하여 연결하므로 실제 테스트(SQL Injection을 유발하는 value값을 테스트)한 결과 Injection의 위험은 없었다

*일반 동적조회조건을 위한 프로시저를 생성할 시 현재까지는 coalesce방식이 작성도 쉽고, 유지보수도 쉬우므로 이 방식을 택하는 것으로한다(더 나은 방식이있다면 제시해주세요..please!!)
하지만, 이렇게 해도 수행속도가 나빠진다면 다이나믹프로시저(sp_executesql)를 비교테스트하여
적용을 고려하는 것도 좋은 방법이다.

*SQL2005에서 프로시저가 캐슁되는지 판별방법

SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan'
ORDER BY usecounts DESC
(정적 프로시저 , 다이나믹프로시저 모두 볼 수있다)

SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype,
SUM (total_worker_time / 1000) AS total_cpu_time_in_ms,
SUM (total_physical_reads) AS total_physical_reads,
SUM (total_logical_reads) AS total_logical_reads,
SUM (total_logical_writes) AS total_logical_writes,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype in ('Proc','Prepared') AND cacheobjtype = 'Compiled Plan'
GROUP BY usecounts, size_in_bytes, cacheobjtype, [text]
ORDER BY usecounts DESC
(정적 프로시저 , 다이나믹프로시저(statment기준)별로 IO(읽기/쓰기) 및 cpu사용시간등을 볼 수있다)

DBCC FREEPROCCACHE
(캐쉬를 모두 지운다)

SET STATISTICS IO ON
SET STATISTICS TIME ON
을 사용하여 IO와 TIME을 볼 수있다

(샘플예)

1. coalesce

http://www.sommarskog.se/dynsearch/search_orders_5.sp

CREATE PROCEDURE search_orders_5

(

@orderid int = NULL,

@fromdate datetime = NULL,

@todate datetime = NULL,

@minprice money = NULL,

@maxprice money = NULL,

@custid nchar(5) = NULL,

@custname nvarchar(40) = NULL,

@city nvarchar(15) = NULL,

@region nvarchar(15) = NULL,

@country nvarchar(15) = NULL,

@prodid int = NULL,

@prodname nvarchar(40) = NULL

)

BEGIN

DECLARE @mincustid nchar(10),

@maxcustid nchar(10),

@minint int,

@maxint int

SELECT @minint = convert(int, 0x80000000),

@maxint = convert(int, 0x7FFFFFFF)

IF @custid IS NULL

SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)

FROM Customers

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,

c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,

c.PostalCode, c.Country, c.Phone, p.ProductID,

p.ProductName, p.UnitsInStock, p.UnitsOnOrder

FROM Orders o

JOIN [Order Details] od ON o.OrderID = od.OrderID

JOIN Customers c ON o.CustomerID = c.CustomerID

JOIN Products p ON p.ProductID = od.ProductID

WHERE o.OrderID BETWEEN coalesce(@orderid, @minint) AND

coalesce(@orderid, @maxint)

AND od.OrderID BETWEEN coalesce(@orderid, @minint) AND

coalesce(@orderid, @maxint)

AND o.OrderDate >= coalesce(@fromdate, '17530101')

AND o.OrderDate <= coalesce(@todate, '99991231')

AND od.UnitPrice >= coalesce(@minprice, 0)

AND od.UnitPrice <= coalesce(@maxprice, 1E18)

AND o.CustomerID BETWEEN coalesce(@custid, @mincustid) AND

coalesce(@custid, @maxcustid)

AND c.CustomerID BETWEEN coalesce(@custid, @mincustid) AND

coalesce(@custid, @maxcustid)

AND c.CompanyName LIKE coalesce(@custname + '%', '%')

AND c.City LIKE coalesce(@city, '%')

AND (c.Region = @region OR @region IS NULL)

AND (c.Country = @country OR @country IS NULL)

AND od.ProductID BETWEEN coalesce(@prodid, @minint) AND

coalesce(@prodid, @maxint)

AND p.ProductID BETWEEN coalesce(@prodid, @minint) AND

coalesce(@prodid, @maxint)

AND p.ProductName LIKE coalesce(@prodname + '%', '%')

ORDER BY o.OrderID

END

2. 다이나믹프로시저(sp_executesql)

http://www.sommarskog.se/dynsearch/search_orders_1.sp

CREATE PROCEDURE search_orders_1

(

@orderid int = NULL,

@fromdate datetime = NULL,

@todate datetime = NULL,

@minprice money = NULL,

@maxprice money = NULL,

@custid nchar(5) = NULL,

@custname nvarchar(40) = NULL,

@city nvarchar(15) = NULL,

@region nvarchar(15) = NULL,

@country nvarchar(15) = NULL,

@prodid int = NULL,

@prodname nvarchar(40) = NULL,

@debug bit = 0

)

BEGIN

DECLARE @sql nvarchar(4000),

@paramlist nvarchar(4000)

SELECT @sql =

'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,

c.CustomerID, c.CompanyName, c.Address, c.City,

c.Region, c.PostalCode, c.Country, c.Phone,

p.ProductID, p.ProductName, p.UnitsInStock,

p.UnitsOnOrder

FROM dbo.Orders o

JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID

JOIN dbo.Customers c ON o.CustomerID = c.CustomerID

JOIN dbo.Products p ON p.ProductID = od.ProductID

WHERE 1 = 1'

IF @orderid IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +

' AND od.OrderID = @xorderid'

IF @fromdate IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'

IF @todate IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'

IF @minprice IS NOT NULL

SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'

IF @maxprice IS NOT NULL

SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'

IF @custid IS NOT NULL

SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +

' AND c.CustomerID = @xcustid'

IF @custname IS NOT NULL

SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''

IF @city IS NOT NULL

SELECT @sql = @sql + ' AND c.City = @xcity'

IF @region IS NOT NULL

SELECT @sql = @sql + ' AND c.Region = @xregion'

IF @country IS NOT NULL

SELECT @sql = @sql + ' AND c.Country = @xcountry'

IF @prodid IS NOT NULL

SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +

' AND p.ProductID = @xprodid'

IF @prodname IS NOT NULL

SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%'''

SELECT @sql = @sql + ' ORDER BY o.OrderID'

IF @debug = 1

PRINT @sql

SELECT @paramlist = '@xorderid int,

@xfromdate datetime,

@xtodate datetime,

@xminprice money,

@xmaxprice money,

@xcustid nchar(5),

@xcustname nvarchar(40),

@xcity nvarchar(15),

@xregion nvarchar(15),

@xcountry nvarchar(15),

@xprodid int,

@xprodname nvarchar(40)'

EXEC sp_executesql @sql, @paramlist,

@orderid, @fromdate, @todate, @minprice,

@maxprice, @custid, @custname, @city, @region,

@country, @prodid, @prodname

END

3. 다이나믹프로시저(sp_executesql)의 OUTPUT(http://www.sqler.com/bSQLQA/624461)

DECLARE @DEL_DATE NVARCHAR(10)

DECLARE @SQL NVARCHAR(4000)

DECLARE @PARAMS NVARCHAR(4000)

SET @SQL = 'SELECT @DEL_DATE = CONVERT(CHAR(10), MIN(CREATE_DATE), 23) FROM ' + @TABLE_NAME + ' WHERE ' + @WHERE_CONDITION;

SET @PARAMS = '@DEL_DATE NVARCHAR(10) OUTPUT'

EXEC SP_EXECUTESQL @SQL, @PARAMS, @DEL_DATE OUTPUT;

참고)

1. Windows & .NET Magazine 2005년 11월

2. http://www.sommarskog.se/dyn-search.html

3. http://www.powerdb.net/?inc=read&aid=506&criteria=mssql&subcrit=&id=&limit=20&keyword=&page=1

[MS-SQL]동적조회 조건에서의 정적 프로시져와 동적프로시져 Dynamic Where