본 문서,당신은 당신을 얻을 것이 기초를 배울의 동적 SQL,어떻게 구축제표에 따라 변수 값을 실행하는 방법을 건설하는 문은 사용하기 전까 및 실행()내에서 저장되는 절차입니다.
이 단원에서 찾은 모든 예제는 Microsoft SQL Server Management Studio 와 AdventureWorks 및 Wideworldimporter 의 샘플 데이터베이스를 기반으로합니다. 당신은 SQL Server 를 사용하여 시작하기,내 가이드를 사용하여 이러한 무료 도구를 사용하여 시작할 수 있습니다.,
저장 프로 시저에서 동적 SQL 을 빌드하십시오.
우리가 쓰는 많은 SQL 은 저장 프로 시저에 명시 적으로 기록됩니다. 이것이 우리가 정적 SQL 이라고 부르는 것입니다. 그러한 것은 변경되지 않기 때문에 명명됩니다. 일단 그것이 쓰여지면,그것은 그것이 돌로 설정되었음을 의미합니다.
다음은 정적 SQL 의 예입니다.
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
여기에 두 개의 문이 있다는 것을 알았습니까? 각 진술은 특정 직원 출생 연도에 대한 직무 요약을 반환합니다. 우리가 더 많은 출생 연도를 추가하고 싶다면 더 많은 진술을 추가해야합니다., 성명서를 한 번만 써야하고 그 해를 즉석에서 바꿀 수 있다면 어떨까요?동적 SQL 이 작동하는 곳입니다.
동적 SQL 은 런타임에 생성되고 실행되는 SQL 입니다. 그것은 복잡한 소리,하지만 정말 그렇지 않습니다. 대신하는 데 문 입력 직접적으로 저장 프로시저 SQL 문을 먼저 내장고에 정의된 변수입니다.그런 다음 이러한 변수의 코드가 실행됩니다. 이제 우리의 예제를 계속하겠습니다.다음은 동적 SQL 을 사용하는 동일한 코드입니다.
동적 SQL 이 녹색으로 강조 표시됩니다., 이것은 각@birthYear 에 대해 빌드 된 SQL 입니다. SQL 이 빌드됨에 따라@문에 저장됩니다. 그런 다음 sp_executesql 을 사용하여 실행되며 아래에서 설명하겠습니다.
sp_executesql 소개
sp_executeslq 를 사용하여 변수 내에 저장된 transact SQL 을 실행할 수 있습니다. 문 양식은
EXECUTE sp_executesql @statement.
궁금한 경우 sp_executesql 은 시스템 저장 프로 시저입니다. 시스템 저장 프로 시저는 언어를 확장하고 사용자가 사용할 수있는 더 많은 기능을 제공합니다.,
여기에 간단한 예제 시:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
를 실행하는 경우 쿼리 창에서 당신을 얻을 것이 유사한 결과 다음과 같다:
2018-01-24 18:49:30.143
이제 당신이 볼 수있는 방법 sp_executeslq 작동,하자에 넣습니다. 우리는 당신이 요구되고 있를 쓰고 저장하는 절차를 반환하거나 평균 LineTotal 또는 합의 LineTotal 여련을 위한 제품에서 발송 2011.
당신의 상사는 이것을 저장 프로 시저로 작성하는 것을 선호합니다. 저장 프로 시저는 하나의 매개 변수@ReturnAverage 를 받아 들여야합니다., 사실이라면 평균을 반환하고 그렇지 않으면 합계를 반환합니다.
의 과정을 작성할 수 있습니다 이 두 가지 별도의와 같이 쿼리에는 다음과 같은 저장 proc 그러나지 않는 것이 훨씬 재미있는,그것은 너무 많은 것을 입력 및 경향하는 오류!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
여기에 나쁜 부분은 내가 녹색으로 채색 한 중복 코드가 많다는 것입니다. 고유 한 코드는별로 없지만 거기에 있다는 것은 빨간색으로 채색되어 있습니다.
이 모든 중복성을 통해 동적 SQL 을 과시 할 수있는 좋은 기회가 있습니다. 그것을 위해 가자!,
여기서 sql 의 두 가지 완전한 버전,하나는 AVG 용,다른 하나는 SUM 용 대신 요청 된 버전을 즉석에서 빌드합니다.
sql 이 빌드되어 변수@문에 저장됩니다. 이 변수는 매개 변수 값@returnAverage 를 기반으로 작성됩니다. 1 로 설정하면@함수는 평균을 나타내며 그렇지 않으면 합산됩니다.
그런 다음 문을 만들기 위해 sql 이 빌드 된 위치를 볼 수 있습니다. 색상 코딩을 주목하십시오. 그것은 정적 버전 내에서 유사한 부분을 대응해야한다;이것은 당신이 비교를 할 수 있도록해야한다.,
동적 SQL 디버깅
런타임에 SQL 이 어떻게 생겼는지 궁금 할 것입니다. 당신은 쉽게 검사하는 코드를 사용하여 디버거:
실행 저장 프로시저를 사용하여 디버거의 명령을 실행한 다음 단계로 코드
단계로 코드를 읽을 때까지 실행 문이 아래와 같습니다.
도달하면 당신이 문에서 마우스를 놓고@문을 때,그리고 도구를 표시 텍스트를 선택이 가능합니다.,디버거는 강력하고 이해할 가치가 있습니다. 만약 당신이 여기에 대해 더 많은 것을 배우도록 매우 격려 할 것입니다.
매개 변수가있는 sp_executesql 사용
sp_executesql 을 사용하여 명령문 내의 매개 변수를 참조 할 수 있습니다. 이것은 궁극적으로 코드를 쉽게 읽을 제공하는 몇 가지 혜택 최적화로 문을 컴파일할 수 있습면 다시 사용됩니다.
문은 형식을 취합니다.
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
그래서 조각을 설명합시다.
- @statement 는 우리가 실행하고자하는 SQL 입니다.,
- @parameterDefinition 은@문에서 참조되는 모든 매개 변수의 정의를 포함하는 문자열입니다. 발견 된 각 매개 변수 및 유형@문이 나열됩니다. 이름과 유형은 공백으로 구분됩니다. 여러 매개 변수는 쉼표로 구분됩니다.
다음으로 매개 변수와 원하는 값을 지정하여 매개 변수 값을 설정합니다. 매개 변수는@parameterDefinition 문자열 내에 정의된 순서대로 나열됩니다.
- @parm1 은@parameterDefinition 문자열 내에 정의 된 첫 번째 매개 변수입니다. 값은 당신이 그것을 설정하고자하는 값입니다.,
- @parm2 는@parameterDefinition 에서 선언 된 것처럼 정의하는 경우 두 번째 매개 변수입니다.
- 다…
여기에 간단한 예제는 추가 두 번호,시:
다양한 부분의 문은 색상 코드:
- @statement(녹색)–공지사항 포함한 2 개의 매개변수:@및@b. 또한 이러한 선언에 TSQL. 오히려 매개 변수 정의에 정의됩니다.
- @parameterDefinition(파란색)–나열된 각 매개 변수는 int 유형으로 정의됩니다.
- 매개 변수 값(빨간색)–여기서 매개 변수의 값을 설정합니다.,
마무리하려면이 예제에서는 두 개의 매개 변수를 추가하는 동적으로 실행 된 SQL 문이 있습니다.
이러한 매개 변수는 정수로 정의됩니다. 각 매개 변수의 값은 sp_executesql 명령에 설정됩니다.
매개 변수가있는 sp_executesql 을 사용하는 예제
이전 예제를 가져 와서 확장 해 보겠습니다. 우리가했던 것처럼 shipdate 를 쿼리에 하드 코딩하기보다는 매개 변수로 가져 가자. 이렇게하면 쿼리가보다 유연 해지고 2011 년 이외의 연도와 함께 작동합니다.,이 변경을 수행하려면 동적 쿼리뿐만 아니라 저장 프로 시저에 매개 변수를 추가하겠습니다. Sp_executesql 명령을 사용하여 이러한 매개 변수를 사용하여 동적 쿼리를 호출합니다.
변경 사항이있는 업데이트 된 저장 프로 시저가 아래에 나와 있습니다. 저장 프로 시저 매개 변수는 녹색이고 동적 쿼리 매개 변수는 빨간색입니다.
실행하는 이에,단순히 uspCalculateSalesSummaryDynamic2proc 쿼리에서 윈도우를 사용하여 다음과 같은 명령:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
이렇게 하는 경우,당신은 다음 결과를 참조하십시오.,
내가 당신을 하고 단순화,의 결합@shipDateYear 및@shipDate 하나로 매개 변수입니다. 우리는 코드에서@shipDateYear 를 제거 할 것입니다. 이것은 쉽게 따라 할 수 읽기:
통지를 실행하는 문은 훨씬 간단하게 할 필요가 없을 할당 SQL 문을 매개 변수@shipDateYear 저장소 절차를 매개 변수@shipDate 의 값입니다.이것은 진술을보다 콤팩트하고 읽기 쉽게 만듭니다., 의 흐름을 읽으로 더 나은 당신이 없을 정신적으로 만들 사이의 연결을 저장 프로시저를 매개 변수 및 SQL 매개 변수를
실행 Dynamic SQL EXECUTE()
사용할 수도 있습니다 EXEC 또는 실행하는 명령을 실행하는 dynamic SQL. 이 명령의 형식
EXECUTE (@statement)
여기에 간단한 예제 시:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
는 것이 중요하 묶@문서 괄호입니다. EXECUTE 문이@문을 가져 가지 않으면 동적 SQL 을 실행하는 대신 변수 값이 저장 프로 시저의 이름이라고 생각합니다., 당신은 다음과 같은 오류가:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
의 물론,이것을 제공합한 힌트! 원하는 경우 변수를 사용하여 호출 할 저장 프로 시저를 지정할 수 있습니다.
sp_executesql 대 EXECUTE
sp_executesql 대 EXECUTE 를 사용하는 이유가 궁금 할 수 있습니다. 둘의 차이점은 무엇입니까?
여기에 몇 가지 이유가 사용하는 것이 좋습기 전까을 실행하는 dynamic SQL:
- 으로 실행한 모든 매개변수의 많은 수에서 변환의 기본 유형을합니다., 이는 동적으로 구축 된 SQL 을 기존 계획과 일치시키는 최적화 프로그램의 기능을 방해합니다.
- 사용해 전까,최적화 프로그램을 인식한 매개 변수 내에서 동적 SQL,쉽게 만들기 위한 최적화 프로그램을 일치 계획입니다.
- 그것들을 통합하는 연결 된 텍스트의 무리를 읽는 것보다 매개 변수화 된 쿼리를 읽는 것이 더 쉽습니다.
- 매개 변수화 된 쿼리는 SQL 주입 공격에 덜 취약합니다.리>