声明查询字符串的变量 [英] Declare Variable for a Query String
问题描述
我想知道MS SQL Server 2005中是否有办法做到这一点:
I was wondering if there was a way to do this in 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 ,然后继续...
It's possible, but it requires using dynamic SQL.
I recommend reading The curse and blessings of dynamic SQL before continuing...
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,例如:
Dynamic SQL is just a SQL statement, composed as a string before being executed. So the usual string concatenation occurs. Dynamic SQL is required whenever you want to do something in SQL syntax that isn't allowed, like:
- 单个参数表示逗号分隔IN子句的值列表
- 表示值和SQL语法的变量(即:您提供的示例)
EXEC sp_executesql
允许您使用bind / preparedstatement参数,因此您不必担心转义单引号/ etc来进行SQL注入攻击
EXEC sp_executesql
allows you to use bind/preparedstatement parameters so you don't have to concern yourself with escaping single quotes/etc for SQL injection attacks.
这篇关于声明查询字符串的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!