Invalid Column Name SQL Server Stored Procedure Error

In the code below there is an error of Invalid Column Name in the Stored Procedure in our SQL. It breaks at this line below...."[CurrWeekBeginDate] int Null
    )"

Can anyone identify what the problem is.....? Please advise. Thank you.


USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_TEST_sproc_RAMS_RB]    Script Date: 09/12/2013 18:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_TEST_sproc_RAMS_RB]
      (
            @Cus_no_Start            varchar(12),
            @Cus_no_End                  varchar(12),
--            @Rams_Cus_no_Start      varchar(12),
--            @Rams_Cus_no_End      varchar(12),
            @DB_Include                  int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS

      ) AS

-- for testing
-- drop table #weeklytemp
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'
-- end testing
/* note:
8 columns
WW = col 1 report_sort = 10
EW = col 2 report_sort = 20
OR = col 3 report_sort = 30
AK = col 4 report_sort = 40
MW = col 5 report_sort = 185
NC = col 6 report_sort = 50
MI = col 7 report_sort = 110
FS = col 8 report_sort = 70
*/
----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*' and @Cus_no_End <> '*'  
BEGIN
  set @Cus_no_Start                  = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End                  = right('000000000000' + @Cus_no_End,12)
END

/***************************************************************************
-- Taking out the selection for RAMS customers
If @Rams_Cus_no_Start <> '*' and @Rams_Cus_no_End <> '*'
BEGIN
  set @Rams_Cus_no_Start      = right('000000000000' + @Rams_Cus_no_Start,12)
  set @Rams_Cus_no_End            = right('000000000000' + @Rams_Cus_no_End,12)
END
*****************************************************************************/

-- print @Cus_no_Start
-- print @Cus_no_End
----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrFromDate             = CurrFromDate,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate             = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK)

declare @CurrentWeek decimal(8,0),
            @NumberOfWeeks int,
            @CurrToDate_Period tinyint,
            @CurrToDateYTD_FiscalYear smallint
declare @CurrToDate_pSDt datetime,
            @CurrToDate_pSDt7 datetime

select
      @CurrentWeek                         = CurrentWeek,
      @NumberOfWeeks                         = NumberOfWeeks,
      @CurrToDate_Period                   = CurrToDate_Period,
      @CurrToDateYTD_FiscalYear      = CurrToDateYTD_FiscalYear,
      @CurrToDate_pSDt                  = CurrToDate_PeriodStartDate,
      @CurrToDate_pSDt7                  = CurrToDate_PeriodStartDate+(7*vwFiscalDates.NumberOfWeeks-1)

from dbo.vwFiscalDates  WITH (NOLOCK)


----------------------------------------------------------------------------------------------------
--  below is pulling data from macola into temp table weeklytemp
CREATE TABLE #weeklytemp (
      [CurrWeekEndDate] [int] NULL ,
      [CurrToDate] [int] NULL ,
      [CurrToDateYTD] [int] NULL ,
      [CurrentWeek] [decimal](8, 0) NULL ,
      [NumberOfWeeks] [int] NULL ,
      [CurrToDate_Period] [tinyint] NULL ,
      [CurrToDateYTD_FiscalYear] [smallint] NULL ,
      [Terr_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Report_Sort] [int] NULL ,
      [Terr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustNo] [varchar] (24)       COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CustName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,      
      [TerrAdopted] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Adopted_Sort] [int] NULL ,
      [WeekNumber] [int] NULL ,
      [Week1_EndDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TICKET_NUMBER] varchar (10) NULL,
      [Item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [RETURN_REASON_CODE] varchar (6) null,
      [FINAL_PRICE] decimal (38,2) null,
      [TOTAL_DELIV_QUANTITY] int null,
      [Week_Sls_Amt] [decimal](38, 2) NULL ,
      [Week_Cost_Amt] [decimal](38, 2) NULL,
      [Date_id] [datetime] null,
      [qty_to_ship] varchar (10) null,
      [inv_dt] datetime,  
      [prod_cat] int, --left([prod_cat],2) int, --as ProdCatNo,
      
    [CurrWeekBeginDate] int Null
    )
   
   -- @PriorWeekBeginDate [Int] Null,
   -- @PriorWeekEndDate [Int] Null
      --   end pulling data into temp table
      
      
--------------------------------------------------------
--RAMS Section
-------------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both Macola and RAMS data
BEGIN

insert  into #weeklytemp

      SELECT
            @CurrWeekEndDate as CurrWeekEndDate,
            @CurrToDate as CurrToDate,
            @CurrToDateYTD as CurrToDateYTD,
            @CurrentWeek as CurrentWeek,
            @NumberOfWeeks as NumberOfWeeks,
            @CurrToDate_Period as CurrToDate_Period,
            @CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
            ID.ID_DESC as Terr_name,
--            Right(ID.IDVALUE,4) as Rpt_Sort,
            RptTerr.Report_Sort,
            SUBSTRING(ID.ID_DESC, 3, 2) AS Terr,
            OH.OH_CUSTOMER_NUMBER,
            CM.CM_FULLNAME,
            Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
            Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
            Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
            FLOOR((CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt,112))/7+1) AS WeekNumber,
            CONVERT(char(8), @CurrToDate_pSDt+(7*FLOOR(CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt)/7+1)-1),112) AS Week1_EndDate,
            OD_TICKET_NUMBER,
            OD.OD_PRODUCT as Item_no,
            OD.OD_RETURN_REASON_CODE,
            OD.OD_FINAL_PRICE,
            OD.OD_TOTAL_DELIV_QUANTITY,
            Week_Sls_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                      AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) -- Sale or BuyBack
                                             THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                             ELSE 0 END),
            Week_Cost_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001'))                                                                                                                                                                   
                                             THEN      COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                             ELSE 0 END),
           OH.OH_delivery_date,
           OD_TOTAL_DELIV_QUANTITY,
           OH.OH_Delivery_Date,
         PD.PRD_A_GLTYPE ,  --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
            CurrWeekBeginDate
            --[PriorWeekBeginDate] ,
            --[PriorWeekEndDate]
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  --WITH NOLOCK
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  --WITH NOLOCK
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM.CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as ID  --WITH NOLOCK
                  ON CM.CM_SELLZONE = ID.IDVALUE
            INNER JOIN dbo.Rpt_Territory as RptTerr on RptTerr.Terr_id  = SUBSTRING(ID.ID_DESC, 3, 2) --WITH NOLOCK
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD       --WITH  (NOLOCK)
                  ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER                                                
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD -- WITH (NOLOCK)
                  ON PD.PRD_PRODUCT = OD.OD_PRODUCT      
            
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted  WITH (NOLOCK)
                  ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no
            
            WHERE
                  --(CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrToDate_pSDt AND @CurrToDate_pSDt7)      
                   OH.OH_SALE_TYPE = 'O'
                  AND (ID.ID_TITLE = N'Sale Zone')
END

----------------------------------------------------
--End RAMS Section
----------------------------------------------------


----------------------------------------------------
-- final select
----------------------------------------------------
select * from #weeklytemp
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lawrence BarnesCommented:
In the section below the column CurrWeekEndDate is used twice and would error.

@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrFromDate             = CurrFromDate,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate             = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK)

Less likely...
What version of SQL are you running?  if before 2008, in the section below I think each line needs its own declare.

declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
It is a 2008 Server....so I do not think we need it.....right...?
I am surprised we never got an error before.
0
Lawrence BarnesCommented:
Ok.  So then it is the repeated column name from above and this also aligns with your error message.

select
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrFromDate             = CurrFromDate,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate             = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lawrence BarnesCommented:
Yes...since you have SQL 2008 the declare as you have it is fine.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Gosh how do coders ever figure out they have duplicate rows like that. There has to be a tool that will help find anomalies in the code like duplicates or something.....any ideas on simple tools.
By the way thank you sir......!
0
PortletPaulfreelancerCommented:
:) a text editor that supports sorting?

and/or declare in alpha order (can make life easier in the long run)

this sorted list by Notepad++
   @CurrFromDate             = CurrFromDate,
   @CurrFromDateYTD            = CurrFromDateYTD,
   @CurrToDate                   = CurrToDate,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorFromDate             = PriorFromDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDate             = PriorToDate
   @PriorToDateYTD             = PriorToDateYTD,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,

Open in new window

but the point is taken - a great IDE would at least warn you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.