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

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')

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

If you mean something else, then adding DECLARE @StartDate DATETIME, @EndDate DATETIME makes that statement parse fine, is it the entirety of the SQL?

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.

CREATE VIEW vwImportant_Users ASWITH params AS (    SELECT     varType='%Admin%',     varMinStatus=1)SELECT status, name     FROM sys.sysusers, params    WHERE status > varMinStatus OR name LIKE varTypeSELECT * FROM vwImportant_Users

yielding output:

status  name12      dbo0       db_accessadmin0       db_securityadmin0       db_ddladmin

also via 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

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)SELECT status, name     FROM sys.sysusers CROSS APPLY params    WHERE status > varMinStatus OR name LIKE varType