SQL 为查询字符串声明变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3833352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declare Variable for a Query String
提问by StealthRT
I was wondering if there was a way to do this in MS SQL Server 2005:
我想知道是否有办法在 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
Is this possible to do?
这是可能的吗？
回答by OMG Ponies
It's possible, but it requires using dynamic SQL.
I recommend reading The curse and blessings of dynamic SQLbefore continuing...
这是可能的，但它需要使用动态 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)
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:
动态 SQL 只是一个 SQL 语句，在执行之前组成一个字符串。所以通常的字符串连接发生了。每当您想使用 SQL 语法执行不允许的操作时，就需要使用动态 SQL，例如：
- a single parameter to represent comma separated list of values for an IN clause
- a variable to represent both value and SQL syntax (IE: the example you provided)
- 表示 IN 子句的逗号分隔值列表的单个参数
- 一个变量来表示值和 SQL 语法（IE：你提供的例子）
EXEC sp_executesqlallows you to use bind/preparedstatement parameters so you don't have to concern yourself with escaping single quotes/etc for SQL injection attacks.
EXEC sp_executesql允许您使用 bind/preparedstatement 参数，因此您不必担心在 SQL 注入攻击中转义单引号等。
回答by hunter
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)
)
回答by Somnath Muluk
Using EXEC
使用 EXEC
You can use following example for building SQL statement.
您可以使用以下示例来构建 SQL 语句。
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
使用 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
回答by DavidG
I will point out that in the article linked in the top rated answer The Curse and Blessings of Dynamic SQLthe author states that the answer is not to use dynamic SQL. Scroll almost to the end to see this.
我将指出，在评分最高的答案The Curse and Blessings of Dynamic SQL 中链接的文章中，作者指出答案是不要使用动态 SQL。几乎滚动到最后才能看到这一点。
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.
当然，一旦列表在表中，您就可以使用连接。我无法直接评论评分最高的答案，所以我只添加了这条评论。
