我如何“声明标量变量"?在 Sql Server 中的 VIEW (2005)

本文介绍了我如何“声明标量变量"?在 Sql Server 中的 VIEW (2005)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

SQL 代码是这样工作的(我在 VS2008 中使用它)，但在 SQL Server 中我无法保存它，作为错误消息声明标量变量@StartDate"和声明标量变量@结束日期"弹出.

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.

代码如下:

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

而且我的问题当然是 - 我应该如何声明它们?

我尝试将以下内容放在代码中:

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:

不支持声明游标 SQL 构造或语句."

"The Declare cursor SQL construct or statement is not supported."

推荐答案

正如 Alex K 所提到的，您应该将其编写为内联表值函数.这是描述它的文章.

As Alex K has mentioned, you should write it as a inline table valued function. Here is the article that describes about it.

简而言之，语法类似于

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

您可以有一个选择查询(无论多么复杂，都可以使用 CTE).然后你会用它作为

You can have one select query (however complex, can use CTE). And then you will use it as

SELECT * FROM dbo.GetForPeriod('1-Jan-2010', '31-Jan-2010')