Jack Johnstone Jack Johnstone - 4 months ago 17
SQL Question

How do I "Declare the scalar variable" in a VIEW in Sql Server (2005)

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

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.

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."

Answer

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

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

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