声明查询字符串的变量

Question

我想知道在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

这可能吗？

Answer 1

有可能，但是需要使用动态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注入攻击转义单引号/
etc。