쿼리 문자열에 대한 변수 선언

쿼리 문자열에 대한 변수 선언

---

MS SQL Server 2005에서이 작업을 수행하는 방법이 있는지 궁금합니다.

  DECLARE @theDate varchar(60)
  SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''

  SELECT    AdministratorCode, 
            SUM(Total) as theTotal, 
            SUM(WOD.Quantity) as theQty, 
            AVG(Total) as avgTotal, 
            (SELECT SUM(tblWOD.Amount)
                FROM tblWOD
                JOIN tblWO on tblWOD.OrderID = tblWO.ID
                WHERE tblWO.Approved = '1' 
                AND tblWO.AdministratorCode = tblWO.AdministratorCode
                AND tblWO.OrderDate BETWEEN @theDate
            )
 ... etc

이것이 가능합니까?

---

가능하지만 동적 SQL을 사용해야합니다. 계속하기 전에 동적 SQL의 저주와 축복을
읽는 것이 좋습니다 .

DECLARE @theDate varchar(60)
SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''

DECLARE @SQL VARCHAR(MAX)  
SET @SQL = 'SELECT AdministratorCode, 
                   SUM(Total) as theTotal, 
                   SUM(WOD.Quantity) as theQty, 
                   AVG(Total) as avgTotal, 
                  (SELECT SUM(tblWOD.Amount)
                     FROM tblWOD
                     JOIN tblWO on tblWOD.OrderID = tblWO.ID
                    WHERE tblWO.Approved = ''1''
                      AND tblWO.AdministratorCode = tblWO.AdministratorCode
                      AND tblWO.OrderDate BETWEEN '+ @theDate +')'

EXEC(@SQL)

동적 SQL은 실행되기 전에 문자열로 구성된 SQL 문입니다. 따라서 일반적인 문자열 연결이 발생합니다. 다음과 같이 허용되지 않는 SQL 구문을 수행하려면 동적 SQL이 필요합니다.

• IN 절에 대해 쉼표로 구분 된 값 목록을 나타내는 단일 매개 변수
• 값과 SQL 구문을 모두 나타내는 변수 (IE : 제공 한 예)

EXEC sp_executesql bind / preparedstatement 매개 변수를 사용할 수 있으므로 SQL 인젝션 공격을 위해 작은 따옴표 등을 이스케이프 처리 할 필요가 없습니다.

---

DECLARE @theDate DATETIME
SET @theDate = '2010-01-01'

Then change your query to use this logic:

AND 
(
    tblWO.OrderDate > DATEADD(MILLISECOND, -1, @theDate) 
    AND tblWO.OrderDate < DATEADD(DAY, 1, @theDate)
)

---

Using EXEC

You can use following example for building SQL statement.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

Using sp_executesql

With using this approach you can ensure that the data values being passed into the query are the correct datatypes and avoind use of more quotes.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Reference

---

I will point out that in the article linked in the top rated answer The Curse and Blessings of Dynamic SQL the author states that the answer is not to use dynamic SQL. Scroll almost to the end to see this.

From the article: "The correct method is to unpack the list into a table with a user-defined function or a stored procedure."

Of course, once the list is in a table you can use a join. I could not comment directly on the top rated answer, so I just added this comment.

참고URL : https://stackoverflow.com/questions/3833352/declare-variable-for-a-query-string