January 11, 2010 at 9:17 am
Using SQL Server 2000, (8.0.760)--not upgradable...
Trying to compile a stored procedure that uses variables to determine the table name in the FROM clause of a select statement. Getting 'Msg 137, Level 15, State 2', 'Must declare the variable'.
I'm declaring the "local" variables within the stored procedure, but for some reason it doesn't recognize them...
IF OBJECT_ID ('dbo.RepostOTData', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[RepostOTData];
GO
CREATE PROCEDURE [dbo].[RepostOTData]
@SelectYear VARCHAR(4)
AS
DECLARE @DemandProduct VARCHAR(39),
@OrderSummary VARCHAR(41),
@ShipmentProduct VARCHAR(44)
SET @DemandProduct = 'DWDemand.dbo.T_Fact_Demand_Product_' + @SelectYear;
SET @OrderSummary = 'DWShipments.dbo.T_Fact_Order_Summary_' + @SelectYear;
SET @ShipmentProduct = 'DWShipments.dbo.T_Fact_Shipment_Product_' + @SelectYear;
With the declarations done, I want to insert data into temp tables (they've been created in the stored procedure as well, I just left them out of this post) using the table name that is based on the variable set above.
INSERT INTO #Repost_DemProd (
Order_Nbr_CH_PK,
Line_Item_Seq_Nbr_SI_PK,
Product_Nbr_VC_FK,
Book_Version_VC_FK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
Order_Date_SD_FK,
Ship_Via_CH_FK,
Bill_State_CH_FK,
Bill_Zip_CH_FK,
Ship_State_CH_FK,
Ship_Zip_CH_FK,
Customer_Nbr_CH,
Qty_Ordered_SI,
Ext_Unit_Price_SM,
Ext_Line_Item_Discounts_SM,
Ext_Unit_Price_Less_Line_Item_Discounts_SM,
Ext_Unit_Cost_SM,
Drop_Shipped_CH,
Item_Type_Code_CH,
No_Charge_Code_CH,
Item_Or_Supply_Code_CH,
Order_Type_CH,
Order_Origin_CH,
Extract_Date_SD,
Update_Date_SD,
Price_Range_A_TI_FK,
Cost_Range_A_TI_FK,
Customer_Type_CH_PK
) SELECT Order_Nbr_CH_PK,
Line_Item_Seq_Nbr_SI_PK,
Product_Nbr_VC_FK,
Book_Version_VC_FK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
Order_Date_SD_FK,
Ship_Via_CH_FK,
Bill_State_CH_FK,
Bill_Zip_CH_FK,
Ship_State_CH_FK,
Ship_Zip_CH_FK,
Customer_Nbr_CH,
Qty_Ordered_SI,
Ext_Unit_Price_SM,
Ext_Line_Item_Discounts_SM,
Ext_Unit_Price_Less_Line_Item_Discounts_SM,
Ext_Unit_Cost_SM,
Drop_Shipped_CH,
Item_Type_Code_CH,
No_Charge_Code_CH,
Item_Or_Supply_Code_CH,
Order_Type_CH,
Order_Origin_CH,
Extract_Date_SD,
Update_Date_SD,
Price_Range_A_TI_FK,
Cost_Range_A_TI_FK,
Customer_Type_CH_PK
FROM @DemandProduct dp
INNER JOIN T_Stage_ListToRepost
ON dp.Order_Nbr_CH_PK = T_Stage_ListToRepost.Order_Nbr
--step two OrdSum data
INSERT INTO #Repost_OrdSum (
Order_Nbr_CH_PK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
State_CH_FK,
Zip_CH_FK,
Customer_Nbr_CH,
Order_Date_SD_FK,
Order_Origin_CH_FK,
Order_Type_CH_FK,
Number_Of_Shipments_SI,
Number_Of_Boxes_SI,
Date_Of_First_Shipment_SD,
Date_Of_Last_Shipment_SD,
Postage_Revenue_SM,
Postage_Cost_SM,
Misc_Revenue_SM,
Order_Level_Discounts_SM,
Demand_Units_SI,
Ordered_Free_Units_SI,
Demand_Dollars_SM,
Discontinued_Units_SI,
Discontinued_Dollars_SM,
Shipped_Units_SI,
Shipped_Dollars_SM,
Shipped_Free_Units_SI,
Shipped_Insert_Units_SI,
Shipped_Resend_Units_SI,
Product_Dollars_Shipped_Cost_SM,
Product_Dollars_Resend_Cost_SM,
Weight_Of_Shipments_DC,
Book_Version_VC_FK,
Extract_Date_SD,
Update_Date_SD,
First_Shipment_Days_CH_FK,
Last_Shipment_Days_CH_FK,
Order_Status_CH_FK,
Shipped_Enhancement_Units_SI,
Dem_AOV_Range_A_TI_FK,
Ship_AOV_Range_A_TI_FK,
Discontinued_Insert_Units_SI,
Discontinued_Free_Units_SI,
Ship_Via_CH,
Discontinued_Resend_Units_SI,
Customer_Type_CH_PK
) SELECT Order_Nbr_CH_PK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
State_CH_FK,
Zip_CH_FK,
Customer_Nbr_CH,
Order_Date_SD_FK,
Order_Origin_CH_FK,
Order_Type_CH_FK,
Number_Of_Shipments_SI,
Number_Of_Boxes_SI,
Date_Of_First_Shipment_SD,
Date_Of_Last_Shipment_SD,
Postage_Revenue_SM,
Postage_Cost_SM,
Misc_Revenue_SM,
Order_Level_Discounts_SM,
Demand_Units_SI,
Ordered_Free_Units_SI,
Demand_Dollars_SM,
Discontinued_Units_SI,
Discontinued_Dollars_SM,
Shipped_Units_SI,
Shipped_Dollars_SM,
Shipped_Free_Units_SI,
Shipped_Insert_Units_SI,
Shipped_Resend_Units_SI,
Product_Dollars_Shipped_Cost_SM,
Product_Dollars_Resend_Cost_SM,
Weight_Of_Shipments_DC,
Book_Version_VC_FK,
Extract_Date_SD,
Update_Date_SD,
First_Shipment_Days_CH_FK,
Last_Shipment_Days_CH_FK,
Order_Status_CH_FK,
Shipped_Enhancement_Units_SI,
Dem_AOV_Range_A_TI_FK,
Ship_AOV_Range_A_TI_FK,
Discontinued_Insert_Units_SI,
Discontinued_Free_Units_SI,
Ship_Via_CH,
Discontinued_Resend_Units_SI,
Customer_Type_CH_PK
FROM @OrderSummary os
INNER JOIN T_Stage_ListToRepost
ON os.Order_Nbr_CH_PK = T_Stage_ListToRepost.Order_Nbr
--step three ShipProd data
INSERT INTO #Repost_ShipProd (
Order_Nbr_CH_PK,
Line_Item_Seq_Nbr_SI_PK,
Box_Nbr_SI_PK,
Product_Nbr_VC_FK,
Book_Version_VC_FK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
Order_Date_SD_FK,
Carrier_VC_FK,
State_CH_FK,
Zip_CH_FK,
Customer_Nbr_CH,
Ship_Date_SD,
Order_Origin_CH_FK,
Order_Type_CH_FK,
Shipment_Nbr_SI,
Qty_Shipped_SI,
Ext_Unit_Price_SM,
Ext_Line_Item_Discounts_SM,
Ext_Unit_Price_Less_Line_Item_Discounts_SM,
Ext_Unit_Cost_SM,
Item_Type_Code_CH_FK,
No_Charge_Code_CH_FK,
Item_Or_Supply_Code_CH_FK,
Drop_Shipped_CH,
Extract_Date_SD,
Update_Date_SD,
Ship_Date_Value_CH,
Price_Range_A_TI_FK,
Cost_Range_A_TI_FK,
Ordered_Dollars_SM,
Qty_Ordered_SI,
Customer_Type_CH_PK
) SELECT Order_Nbr_CH_PK,
Line_Item_Seq_Nbr_SI_PK,
Box_Nbr_SI_PK,
Product_Nbr_VC_FK,
Book_Version_VC_FK,
Promo_Year_CH_FK,
Source_Code_CH_FK,
Order_Date_SD_FK,
Carrier_VC_FK,
State_CH_FK,
Zip_CH_FK,
Customer_Nbr_CH,
Ship_Date_SD,
Order_Origin_CH_FK,
Order_Type_CH_FK,
Shipment_Nbr_SI,
Qty_Shipped_SI,
Ext_Unit_Price_SM,
Ext_Line_Item_Discounts_SM,
Ext_Unit_Price_Less_Line_Item_Discounts_SM,
Ext_Unit_Cost_SM,
Item_Type_Code_CH_FK,
No_Charge_Code_CH_FK,
Item_Or_Supply_Code_CH_FK,
Drop_Shipped_CH,
Extract_Date_SD,
Update_Date_SD,
Ship_Date_Value_CH,
Price_Range_A_TI_FK,
Cost_Range_A_TI_FK,
Ordered_Dollars_SM,
Qty_Ordered_SI,
Customer_Type_CH_PK
FROM @ShipmentProduct sp
INNER JOIN T_Stage_ListToRepost
ON sp.Order_Nbr_CH_PK = T_Stage_ListToRepost.Order_Nbr
Thanks in advance for any assistance with this issue.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 11, 2010 at 9:21 am
You have to use "dynamic SQL" for that kind of thing.
Create the select as a string variable (varchar or nvarchar), and execute the string.
Here's a quick sample:
declare @Cmd varchar(max);
set @Cmd = 'select Col1 from dbo.MyTable;';
exec (@Cmd);
You can use the table name variables to build the executable string.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2010 at 9:50 am
Thank you GSqaured! That worked...but what a pain!!!
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 12, 2010 at 6:57 am
Yep, and you're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply