Turn statement into View - table variable declare error 2

[cwsstins]

I've got a query that works perfectly when run on its own and returns what I need it to. When I try to wrap it into a view as displayed below, I'm getting an error "Must declare the table variable "@AwdYrTbl"

I'm sure this is some kind of syntax thing I'm doing wrong. How can I fix it?

-- DECLARE VARIABLES
Declare @TodayYr VARCHAR (4)
Declare @TodayMn VARCHAR (2)
Declare @2Years VARCHAR (4)
Declare @LastYr VARCHAR (4)
Declare @NextYr VARCHAR (4)
Declare @AwdYr1 VARCHAR (7)
Declare @AwdYr2 VARCHAR (7)
Declare @AwdYr3 VARCHAR (7)
Declare @AwdYr4 VARCHAR (7)

-- DECLARE VARIABLE TABLE TO STORE THE YEARS
DECLARE @AwdYrTbl TABLE
(AwdYr VARCHAR(7),
 YearID VARCHAR(2),  
 Nbr VARCHAR(1))

-- SET VARIABLES
SET @TodayYr = YEAR(getdate())
SET @TodayMn = MONTH(getdate())
SET @2Years = YEAR(getdate())-2
SET @LastYr = YEAR(getdate())-1
SET @NextYr = YEAR(getdate())+1

-- SET AWARD YEARS
SET @AwdYr1 = @2Years + '-' + RIGHT(@LastYr,2)
SET @AwdYr2 = @LastYr + '-' + RIGHT(@TodayYr,2)
SET @AwdYr3 = @TodayYr + '-' + RIGHT(@NextYr,2)
SET @AwdYr4 = @NextYr + '-' + RIGHT(@NextYr + 1,2)

-- POPULATE THE VARIABLE TABLE WITH YEAR VALUES ABOVE BASED ON RUN-TIME MONTH
IF @TodayMn IN (1)
  INSERT INTO @AwdYrTbl 
  SELECT @AwdYr1,RIGHT(@AwdYr1,2),'1' UNION ALL SELECT @AwdYr2,RIGHT(@AwdYr2,2),'2'
ELSE IF @TodayMn IN (2,3,4)
  INSERT INTO @AwdYrTbl 
  SELECT @AwdYr1,RIGHT(@AwdYr1,2),'1' UNION ALL SELECT @AwdYr2,RIGHT(@AwdYr2,2),'2' UNION ALL SELECT @AwdYr3,RIGHT(@AwdYr3,2),'3'
ELSE INSERT INTO @AwdYrTbl
  SELECT @AwdYr2,RIGHT(@AwdYr2,2),'1' UNION ALL SELECT @AwdYr3,RIGHT(@AwdYr3,2),'2'

 SELECT * FROM @AwdYrTbl

EXEC('CREATE VIEW vw_Dynamic_Year AS SELECT * FROM @AwdYrTbl')

 

[philhege]

Views must be based on physical tables. See

http://msdn.microsoft.com/en-us/library/ms187956.aspx

If you plan to base the data on the date, you could run the procedure once per time period needed to populate the table.

-----------
With business clients like mine, you'd be better off herding cats.

 

[flapeyre]

Create a table-valued function:

CREATE FUNCTION dbo.GetAwdYrTbl ()
RETURNS @Awdyrtbl TABLE (
	AwdYr VARCHAR(7)
	, YearID VARCHAR(2)
	, Nbr VARCHAR(1)
	)

BEGIN
	DECLARE @TodayYr VARCHAR(4)
		, @TodayMn VARCHAR(2)
		, @2Years VARCHAR(4)
		, @LastYr VARCHAR(4)
		, @NextYr VARCHAR(4)
		, @Awdyr1 VARCHAR(7)
		, @Awdyr2 VARCHAR(7)
		, @Awdyr3 VARCHAR(7)
		, @Awdyr4 VARCHAR(7)

	SET @TodayYr = YEAR(GETDATE())
	SET @TodayMn = MONTH(GETDATE())
	SET @2Years = YEAR(GETDATE()) - 2
	SET @LastYr = YEAR(GETDATE()) - 1
	SET @NextYr = YEAR(GETDATE()) + 1
	SET @Awdyr1 = @2Years + '-' + RIGHT(@LastYr, 2)
	SET @Awdyr2 = @LastYr + '-' + RIGHT(@TodayYr, 2)
	SET @Awdyr3 = @TodayYr + '-' + RIGHT(@NextYr, 2)
	SET @Awdyr4 = @NextYr + '-' + RIGHT(@NextYr + 1, 2)

	IF @TodayMn IN (1)
		INSERT INTO @Awdyrtbl
		SELECT @Awdyr1
			, RIGHT(@Awdyr1, 2)
			, '1'
		
		UNION ALL
		
		SELECT @Awdyr2
			, RIGHT(@Awdyr2, 2)
			, '2'
	ELSE
		IF @TodayMn IN (
				2
				, 3
				, 4
				)
			INSERT INTO @Awdyrtbl
			SELECT @Awdyr1
				, RIGHT(@Awdyr1, 2)
				, '1'
			
			UNION ALL
			
			SELECT @Awdyr2
				, RIGHT(@Awdyr2, 2)
				, '2'
			
			UNION ALL
			
			SELECT @Awdyr3
				, RIGHT(@Awdyr3, 2)
				, '3'
		ELSE
			INSERT INTO @Awdyrtbl
			SELECT @Awdyr2
				, RIGHT(@Awdyr2, 2)
				, '1'
			
			UNION ALL
			
			SELECT @Awdyr3
				, RIGHT(@Awdyr3, 2)
				, '2'

	RETURN
END
GO

To call it:

SELECT 
	gayt.AwdYr
,	gayt.YearID
,	gayt.Nbr FROM dbo.GetAwdYrTbl() AS gayt
GO

and it returns:

[tt]AwdYr YearID Nbr
2011-12 12 1
2012-13 13 2
2013-14 14 3[/tt]

-- Francis
Aut viam inveniam aut faciam.

 

[philhege]

Yup. Works in a CREATE VIEW statement.

-----------
With business clients like mine, you'd be better off herding cats.

 

[cwsstins]

THanks guys...the function did the trick!