如何在 Sql Server (2005) 的视图中“声明标量变量”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3475712/
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
How do I "Declare the scalar variable" in a VIEW in Sql Server (2005)
提问by Hyman Johnstone
I′m trying to create a VIEWin SQL Server2005.
我正在尝试在SQL Server2005 中创建一个视图。
The SQL code is working as such (I′m using it in VS2008), but in SQL Server I′m unable to save it, as error message "Declare the scalar variable @StartDate" and "Declare the scalar variable @EndDate" pops up.
SQL 代码是这样工作的（我在 VS2008 中使用它），但在 SQL Server 中我无法保存它，因为错误消息“声明标量变量@StartDate”和“声明标量变量@EndDate”弹出向上。
Here is the code:
这是代码：
WITH Calendar AS (SELECT CAST(@StartDate AS datetime) AS Date
UNION ALL
SELECT DATEADD(d, 1, Date) AS Expr1
FROM Calendar AS Calendar_1
WHERE (DATEADD(d, 1, Date) < @EndDate))
SELECT C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]
FROM Calendar AS C CROSS JOIN
dbo.Country AS C2 LEFT OUTER JOIN
dbo.Requests AS R ON C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID
GROUP BY C.Date, C2.Country
And my question is of course - exactly how should I declare them?
而我的问题当然是-我应该究竟如何申报呢？
I tried to put the following first in the code:
我尝试将以下内容放在代码中：
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
But that didn′t do the trick, just as I expected - it only gave me another pop-up message:
但这并没有奏效，正如我所料——它只给了我另一个弹出消息：
"The Declare cursor SQL construct or statement is not supported."
“不支持声明游标 SQL 构造或语句。”
回答by VinayC
As Alex K has mentioned, you should write it as a inline table valued function. Here is the articlethat describes about it.
正如 Alex K 所提到的，您应该将其编写为内联表值函数。这是描述它的文章。
In short, syntax would be something like
简而言之，语法类似于
CREATE FUNCTION dbo.GetForPeriod
( @StartDate datetime, @EndDate datetime)
RETURNS TABLE
RETURN
SELECT [[ your column list ]]
FROM [[ table list]
WHERE [[some column] BETWEEN @StartDate AND @EndDate
You can have one select query (however complex, can use CTE). And then you will use it as
您可以有一个选择查询（无论多么复杂，都可以使用 CTE）。然后你会用它作为
SELECT * FROM dbo.GetForPeriod('1-Jan-2010', '31-Jan-2010')
回答by Alex K.
If by VIEWyou mean an SQL Server native view (CREATE VIEW ...) then you cannot use local variables at all (you would use a table-valued udf instead).
如果VIEW是指 SQL Server 本机视图 ( CREATE VIEW ...)，那么您根本不能使用局部变量（您将使用表值 udf 代替）。
If you mean something else, then adding DECLARE @StartDate DATETIME, @EndDate DATETIMEmakes that statement parse fine, is it the entirety of the SQL?
如果你的意思是别的，那么添加DECLARE @StartDate DATETIME, @EndDate DATETIME会使该语句解析得很好，它是整个 SQL 吗？
回答by Oleg Melnikov
Here is a sample query that uses CTE to nicely emulate internal variable construction. You can test-run it in your version of SQL Server.
这是一个使用 CTE 很好地模拟内部变量构造的示例查询。您可以在您的 SQL Server 版本中测试运行它。
CREATE VIEW vwImportant_Users AS
WITH params AS (
SELECT
varType='%Admin%',
varMinStatus=1)
SELECT status, name
FROM sys.sysusers, params
WHERE status > varMinStatus OR name LIKE varType
SELECT * FROM vwImportant_Users
yielding output:
产生输出：
status name
12 dbo
0 db_accessadmin
0 db_securityadmin
0 db_ddladmin
also via JOIN
也通过 JOIN
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers INNER JOIN params ON 1=1
WHERE status > varMinStatus OR name LIKE varType
also via CROSS APPLY
也通过 CROSS APPLY
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers CROSS APPLY params
WHERE status > varMinStatus OR name LIKE varType
回答by G Clayton
try replacing all your @X, @Y with A.X and A.Y, add to your code: FROM (SELECT X = 'literalX', Y = 'literalY') A then you have put all your literals in one spot and have only one copy of them.
尝试用 AX 和 AY 替换你所有的 @X、@Y，添加到你的代码中： FROM (SELECT X = 'literalX', Y = 'literalY') A 那么你把所有的文字放在一个地方，只有一个副本其中。
