lucky
3/26/2007 5:35:00 AM
On Mar 25, 8:24 pm, pgouelle...@fuse.net wrote:
> in SQL Server 2000
>
> I have a table defined as below:
>
> CREATE TABLE [tblPFW_Data_Load] (
> [Company] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [PFW Account No] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Account Type] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Report Line] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Account No] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Account Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Account Id] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Store/Corp Account] [nvarchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [YYYYMM] [int] NOT NULL ,
> [PFW Year] [int] NOT NULL ,
> [PFW Month] [int] NOT NULL ,
> [PFW Date] [datetime] NOT NULL ,
> [Account Status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [PFW Actual] [money] NULL ,
> [PFW Plan] [money] NULL ,
> [Actual] [money] NULL ,
> [Plan] [money] NULL ,
> [Total Current Assets] [money] NULL ,
> [Total Current Assets Plan] [money] NULL ,
> [Net Fixed Assets] [money] NULL ,
> [Net Fixed Assets Plan] [money] NULL ,
> [Net Intangible Assets] [money] NULL ,
> [Net Intangible Assets Plan] [money] NULL ,
> [Total Long Term Assets] [money] NULL ,
> [Total Long Term Assets Plan] [money] NULL ,
> [Total Other Assets] [money] NULL ,
> [Total Other Assets Plan] [money] NULL ,
> [Total Assets] [money] NULL ,
> [Total Assets Plan] [money] NULL ,
> [Total Current Liabilities] [money] NULL ,
> [Total Current Liabilities Plan] [money] NULL ,
> [Total Long Term Liabilities] [money] NULL ,
> [Total Long Term Liabilities Plan] [money] NULL ,
> [Total Stockholders Equity] [money] NULL ,
> [Total Stockholders Equity Plan] [money] NULL ,
> [Total Liabilities and Equity] [money] NULL ,
> [Total Liabilities and Equity Plan] [money] NULL ,
> [Total Sales] [money] NULL ,
> [Total Sales Plan] [money] NULL ,
> [Total Cost of Sales] [money] NULL ,
> [Total Cost of Sales Plan] [money] NULL ,
> [Total Gross Profit] [money] NULL ,
> [Total Gross Profit Plan] [money] NULL ,
> [Food Gross Profit] [money] NULL ,
> [Food Gross Profit Plan] [money] NULL ,
> [Beverage Gross Profit] [money] NULL ,
> [Beverage Gross Profit Plan] [money] NULL ,
> [Total Labor Cost] [money] NULL ,
> [Total Labor Cost Plan] [money] NULL ,
> [Total Controllable Expense] [money] NULL ,
> [Total Controllable Expense Plan] [money] NULL ,
> [PACE] [money] NULL ,
> [PACE Plan] [money] NULL ,
> [Marginal Income] [money] NULL ,
> [Marginal Income Plan] [money] NULL ,
> [Total Occupancy Cost] [money] NULL ,
> [Total Occupancy Cost Plan] [money] NULL ,
> [ROP] [money] NULL ,
> [ROP Plan] [money] NULL ,
> [Total Corporate Expense] [money] NULL ,
> [Total Corporate Expense Plan] [money] NULL ,
> [Income from Operations] [money] NULL ,
> [Income from Operations Plan] [money] NULL ,
> [ITDA] [money] NULL ,
> [ITDA Plan] [money] NULL ,
> [EBITDA] [money] NULL ,
> [EBITDA Plan] [money] NULL ,
> [Cash Flow from Operations] [money] NULL ,
> [Cash Flow from Operations Plan] [money] NULL ,
> [Loss From Discontinued Ops] [money] NULL ,
> [Loss From Discontinued Ops Plan] [money] NULL ,
> [Net Income] [money] NULL ,
> [Net Income Plan] [money] NULL ,
> [Cash Flow After Disc Ops] [money] NULL ,
> [Cash Flow After Disc Ops Plan] [money] NULL ,
> [Total TGI Fridays Profit] [money] NULL ,
> [Total TGI Fridays Profit Plan] [money] NULL ,
> [Total Total Karlos Profit] [money] NULL ,
> [Total Total Karlos Profit Plan] [money] NULL ,
> [Total McAlisters Profit] [money] NULL ,
> [Total McAlisters Profit Plan] [money] NULL ,
> [Total Administrative Expenses] [money] NULL ,
> [Total Administrative Expenses Plan] [money] NULL ,
> [Total Overhead] [money] NULL ,
> [Total Overhead Plan] [money] NULL ,
> [Total TGIF Field Operations Expenses] [money] NULL ,
> [Total TGIF Field Operations Expenses Plan] [money] NULL ,
> [Total Karlos Field Operations Expenses] [money] NULL ,
> [Total Karlos Field Operations Expenses Plan] [money] NULL ,
> [Total McAlisters Field Operations Expenses] [money] NULL ,
> [Total McAlisters Field Operations Expenses Plan] [money] NULL ,
> [Total Executive Group] [money] NULL ,
> [Total Executive Group Plan] [money] NULL ,
> [Total Human Resources] [money] NULL ,
> [Total Human Resources Plan] [money] NULL ,
> [Total Business Development] [money] NULL ,
> [Total Business Development Plan] [money] NULL ,
> [Total Unit Development] [money] NULL ,
> [Total Unit Development Plan] [money] NULL ,
> [Total Finance and Accounting] [money] NULL ,
> [Total Finance and Accounting Plan] [money] NULL ,
> [Total Payroll] [money] NULL ,
> [Total Payroll Plan] [money] NULL ,
> [Total IT] [money] NULL ,
> [Total IT Plan] [money] NULL ,
> [Rent and Building Maintenance] [money] NULL ,
> [Rent and Building Maintenance Plan] [money] NULL ,
> [Total BMI Overhead] [money] NULL ,
> [Total BMI Overhead Plan] [money] NULL ,
> [Total Concept Field Operations Expenses] [money] NULL ,
> [Total Concept Field Operations Expenses Plan] [money] NULL ,
> [BMI Headquarters Expenses] [money] NULL ,
> [BMI Headquarters Expenses Plan] [money] NULL ,
> [Total BMI Expenses] [money] NULL ,
> [Total BMI Expenses Plan] [money] NULL ,
> [Hofbrauhaus Allocation] [money] NULL ,
> [Hofbrauhaus Allocation Plan] [money] NULL ,
> [Net BMI Expenses] [money] NULL ,
> [Net BMI Expenses Plan] [money] NULL ,
> [Management Fees Revenue] [money] NULL ,
> [Management Fees Revenue Plan] [money] NULL ,
> [Contract Management Revenue] [money] NULL ,
> [Contract Management Revenue Plan] [money] NULL ,
> [Allocated Cost - Hofbrauhaus ] [money] NULL ,
> [Allocated Cost - Hofbrauhaus Plan] [money] NULL ,
> [Contribution Margin Hofbrauhaus] [money] NULL ,
> [Contribution Margin Hofbrauhaus Plan] [money] NULL ,
> [BGI Total Sales] [money] NULL ,
> [BGI Total Sales Plan] [money] NULL ,
> [BGI Total Cost of Sales] [money] NULL ,
> [BGI Total Cost of Sales Plan] [money] NULL ,
> [BGI Total Gross Profit] [money] NULL ,
> [BGI Total Gross Profit Plan] [money] NULL ,
> [BGI Food Gross Profit] [money] NULL ,
> [BGI Food Gross Profit Plan] [money] NULL ,
> [BGI Beverage Gross Profit] [money] NULL ,
> [BGI Beverage Gross Profit Plan] [money] NULL ,
> [BGI BMI Management Revenue] [money] NULL ,
> [BGI BMI Management Revenue Plan] [money] NULL ,
> [BGI BMI Management Cost] [money] NULL ,
> [BGI BMI Management Cost Plan] [money] NULL ,
> [BGI Total Labor Cost] [money] NULL ,
> [BGI Total Labor Cost Plan] [money] NULL ,
> [BGI Total Controllable Expense] [money] NULL ,
> [BGI Total Controllable Expense Plan] [money] NULL ,
> [BGI PACE] [money] NULL ,
> [BGI PACE Plan] [money] NULL ,
> [BGI Marginal Income] [money] NULL ,
> [BGI Marginal Income Plan] [money] NULL ,
> [BGI Total Occupancy Cost] [money] NULL ,
> [BGI Total Occupancy Cost Plan] [money] NULL ,
> [BGI ROP] [money] NULL ,
> [BGI ROP Plan] [money] NULL ,
> [BGI BMI EBITDA] [money] NULL ,
> [BGI BMI EBITDA Plan] [money] NULL ,
> [BGI Total Corporate Expense] [money] NULL ,
> [BGI Total Corporate Expense Plan] [money] NULL ,
> [BGI Income from Operations] [money] NULL ,
> [BGI Income from Operations Plan] [money] NULL ,
> [BGI ITD&A] [money] NULL ,
> [BGI ITD&A Plan] [money] NULL ,
> [BGI EBITDA] [money] NULL ,
> [BGI EBITDA Plan] [money] NULL ,
> [BGI Cash Flow from Operations] [money] NULL ,
> [BGI Cash Flow from Operations Plan] [money] NULL ,
> [BGI Net Income] [money] NULL ,
> [BGI Net Income Plan] [money] NULL ,
> [BGI Cash Flow after Disc Ops] [money] NULL ,
> [BGI Cash Flow after Disc Ops Plan] [money] NULL ,
> CONSTRAINT [PK_tblPFW_Data_Load] PRIMARY KEY CLUSTERED
> (
> [Company],
> [PFW Account No]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> I am trying dynamically setting two coulmn names in a declare cursor
> statement in a store procedure as follows:
>
> SET @PFW_Data_Load_Cursor = 'DECLARE PFW_Data_Load_Cursor INSENSITIVE
> CURSOR FOR SELECT Company, [Account No], [Account Id], YYYYMM, [PFW
> Year], [PFW Month], [PFW Date], [Account Status], '
> + @tblPFW_Data_Load_Actual_Column_Name + ', ' +
> @tblPFW_Data_Load_Plan_Column_Name +', [PFW Actual], [PFW Plan] FROM
> dbo.tblPFW_Data_Load WHERE ([Account Status] <>
> (Account_Status_Not_To_Load) AND ('
> + @tblPFW_Data_Load_Actual_Column_Name + ' IS NOT NULL) FOR READ ONLY'
>
> Execute @PFW_Data_Load_Cursor
>
> OPEN PFW_Data_Load_Cursor
>
> And I'm recieving the following error message:
>
> Server: Msg 203, Level 16, State 2, Procedure
> spPFW_Update_Balance_Sheet_Aggregate_Line_Values, Line 100 [Microsoft]
> [ODBC SQL Server Driver][SQL Server]The name 'DECLARE
> PFW_Data_Load_Cursor CURSOR FOR SELECT Company, [Account No], [Account
> Id], YYYYMM, [PFW Year], [PFW Month], [PFW Date], [Account Status],
> [Total Current Assets], [Total Current Assets Plan], [PFW Actual],
> [PFW Plan] FROM dbo.tblPFW_Data_Load WHERE ([Account Status] <>
> @Account_Status_Not_To_Load) AND ([Total Current Assets] IS NOT NULL)
> FOR READ ONLY' is not a valid identifier.
>
> What's up?
>
> When I just code the cursor like this:
>
> DECLARE PFW_Data_Load_Cursor INSENSITIVE CURSOR FOR
> SELECT Company, [Account No], [Account Id], YYYYMM, [PFW Year], [PFW
> Month], [PFW Date], [Account Status], [Total Current Assets], [Total
> Current Assets Plan] , [PFW Actual], [PFW Plan]
> FROM dbo.tblPFW_Data_Load'
> WHERE ([Account Status] <> @Account_Status_Not_To_Load) AND (Total
> Current Assets IS NOT NULL)
> FOR READ ONLY
>
> No Error Message is returned!!!!!!!
>
> What am I missing here?
Try this
Execute (@PFW_Data_Load_Cursor)