Reference Table Variable inside a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Reference Table Variable inside a View

Author

Topic

lagyossarian
Starting Member

2 Posts

Posted - 2009-10-09 : 12:30:18

I have view that is bound to a Pivot Table report in Excel 2007. I want to have the whole year's worth of Monthly Reporting Periods displaying in the columns whether I have data there or not. I thought I would do it by creating a Table Variable, populating the table variable with all of the Reporting Periods and then UNION ALL it with the result set from the SELECT statemtn of the view. No matter what I try, the Table Variable is not being seen with in the ALTER VIEW statement. I'm getting he following error:

Msg 1087, Level 15, State 2, Procedure vw_ForemanExpenseReport, Line 69

Must declare the table variable "@ReportPeriod".

My code is below:


USE [DWOR]

GO

/****** Object: View [dbo].[vw_ForemanExpenseReport] Script Date: 10/09/2009 08:16:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

DECLARE @ReportPeriod TABLE

(

Field1 VARCHAR(50),

Field2 VARCHAR(50),

Field3 VARCHAR(50),

Field4 VARCHAR(50),

Field5 VARCHAR(50),

Field6 VARCHAR(50),

Field7 INT,

Field8 VARCHAR(50),

Field9 DATETIME,

Field10 MONEY,

Field11 VARCHAR(50),

Field12 VARCHAR(50),

Period VARCHAR(12)

)

INSERT INTO @ReportPeriod (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Period)

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Jan 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Feb 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Mar 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Apr 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'May 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Jun 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Jul 2009'

UNION 

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Aug 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Sep 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Oct 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Nov 2009'

UNION ALL

SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Dec 2009'

GO

ALTER VIEW [dbo].[vw_ForemanExpenseReport]

AS

SELECT TOP (100) PERCENT 

dbo.AssetTeams.Name AS [Asset Team],

dbo.FieldOffices.Name AS [Field Office],

dbo.Superintendents.Name AS Superintendent,

dbo.Foremen.Name AS Foreman, 

dbo.CostCodes_SubExpenseCategories.ExpenseCategory, 

dbo.SubExpenseCategories.Category, 

dbo.CostCodes.CostCodeID, 

dbo.CostCodes.[Description] AS [Cost Code], 

dbo.Expenses.ExpenseDate, 

dbo.Expenses.ExpenseAmount, 

dbo.Expenses.InvoiceNumber, 

dbo.Vendors.Name AS Vendor, 

dbo.Expenses.ReportPeriod


FROM 

dbo.Expenses 

INNER JOIN dbo.CostCodes (NOLOCK)

ON dbo.Expenses.CostCodeID = dbo.CostCodes.CostCodeID 

INNER JOIN dbo.CostCodes_SubExpenseCategories (NOLOCK)

ON dbo.CostCodes.CostCodeID = dbo.CostCodes_SubExpenseCategories.CostCodeID

INNER JOIN dbo.SubExpenseCategories (NOLOCK)

ON dbo.CostCodes_SubExpenseCategories.SubExpenseCategoryID = dbo.SubExpenseCategories.SubExpenseID

LEFT JOIN dbo.Foremen (NOLOCK)

ON dbo.Expenses.ForemanID = dbo.Foremen.ForemanID 

LEFT JOIN Superintendents_Foremen (NOLOCK)

ON Foremen.ForemanID = Superintendents_Foremen.ForemanID

LEFT JOIN Superintendents (NOLOCK)

ON Superintendents_Foremen.SuperintendentID = Superintendents.SuperintendentID

LEFT JOIN FieldOffices_Superintendents (NOLOCK)

ON Superintendents.SuperintendentID = FieldOffices_Superintendents.SuperintendentID

LEFT JOIN FieldOffices (NOLOCK)

ON FieldOffices_Superintendents.FieldOfficeID = FieldOffices.FieldOfficeID

LEFT JOIN AssetTeams_FieldOffices (NOLOCK)

ON FieldOffices.FieldOfficeID = AssetTeams_FieldOffices.FieldOfficeID

LEFT JOIN AssetTeams (NOLOCK)

ON AssetTeams_FieldOffices.AssetTeamID = AssetTeams.AssetTeamID

LEFT JOIN dbo.Vendors (NOLOCK)

ON dbo.Expenses.VendorID = dbo.Vendors.VendorID 


GROUP BY 

dbo.AssetTeams.Name,

dbo.FieldOffices.Name,

dbo.Superintendents.Name,

dbo.Foremen.Name, 

dbo.CostCodes_SubExpenseCategories.ExpenseCategory, 

dbo.SubExpenseCategories.Category, 

dbo.CostCodes.CostCodeID, 

dbo.CostCodes.[Description], 

dbo.Expenses.ExpenseDate, 

dbo.Expenses.ExpenseAmount, 

dbo.Expenses.InvoiceNumber, 

dbo.Vendors.Name, 

dbo.Expenses.ReportPeriod


HAVING 

(dbo.Foremen.Name IS NOT NULL) AND

(dbo.Expenses.ExpenseDate >= '1/1/2009') AND (dbo.Expenses.ExpenseDate < '1/1/2010')

ORDER BY 

dbo.CostCodes.CostCodeID


UNION ALL

SELECT * FROM @ReportPeriod

GO