如何在Sql Server（2005）的VIEW中“声明标量变量”

I´m trying to create a VIEW in SQL Server 2005.

我正在尝试在SQL Server 2005中创建一个VIEW。

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代码是工作本身（I'm使用它在VS2008），但在SQL Server I'm无力，保存为错误消息“声明标量变量@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."

“不支持Declare游标SQL构造或语句。”

4 个解决方案

#1

As Alex K has mentioned, you should write it as a inline table valued function. Here is the article that 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')

#2

If by VIEW you 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 DATETIME makes that statement parse fine, is it the entirety of the SQL?

如果您的意思是其他的，那么添加DECLARE @StartDate DATETIME，@ EndDate DATETIME使该语句解析得很好，它是整个SQL吗？

#3

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

#4

-1

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'）然后你把所有的文字放在一个地方，只有一个副本他们

#1