Code only returns Headers no records.......what can I look for

When a Stored Procedure Executes but only brings back the headers. I would assume that the first thing to check is the Parameters and filters.......I do not think these are factors as they are so few. Then I would think it would be something in the links that are broken. Does anyone see anything that might be suspect in this Stored Procedure.

USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_RB]    Script Date: 10/01/2013 09:31:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_RB] (@Cus_no_Start varchar(12), @Cus_no_End varchar(12)) AS

-- for testing

-- drop table #SalesSummary
-- 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

----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*'
BEGIN
  set @Cus_no_Start = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End   = right('000000000000' + @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
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select
   @CurrentWeek       = CurrentWeek,
   @NumberOfWeeks       = NumberOfWeeks,
   @CurrToDate_Period       = CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary
(
      --[data_source] varchar(12),--  what database is the data coming from  macola or rams?
      PerType varchar(18) NULL,
      Terr char(2) NULL,
      Item_no varchar(15),
      Gross_Sales decimal(38,2) NULL,
      Cost decimal(38,2) NULL,
      prod_cat Varchar (12),
      Cus_No Varchar (12)
      
      
)


----------------------------------------------------
-- insert for MTD Current and Prior
----------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,  -- added RB
    ARCUSFIL_SQL.Cus_No    
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No

------------------------------------------------------
---- insert for MTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,

      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No
------------------------------------------------------
---- insert for YTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '3 - YTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
      WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No

INSERT INTO #SalesSummary
SELECT

      '3 - YTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
   OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
   OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No
----------------------------------------------------
-- final select
----------------------------------------------------

SELECT

      
      SS.PerType,
      CM.CM_SELLZONE,
      @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,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort,
      SS.Terr,
      SS.Item_no,
      Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
      sum(isnull(SS.[Gross_Sales],0))  AS Gross_Sales,
      sum(isnull(SS.[Cost],0))  AS Cost,
      PD.PRD_A_GLTYPE   -- Added RB
      --,sum(isnull(SS.[Prior_Gross_Sales],0))  AS Prior_Gross_Sales,sum(isnull(SS.[Prior_Cost],0))  AS Prior_Cost
FROM #SalesSummary AS SS
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                  ON PD.PRD_PRODUCT = SS.Prod_cat
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                         ON CM.CM_CUSTKEY = SS.Cus_No
      INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
      LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no

WHERE Rpt_Territory.ActiveFlag = 'Y'
GROUP BY SS.PerType,
     CM.CM_SELLZONE,
       Rpt_Territory.Terr_name,
       Rpt_Territory.Report_Sort,
       SS.Terr,
       SS.Item_no,
          Rpt_TerritoryAdopted.Terr_id,
          PD.PRD_A_GLTYPE   -- Added RB
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>When a Stored Procedure Executes but only brings back the headers.
That means the SP successfully executed and return zero rows.

I'd start commenting out WHERE clauses and executing to find the error.

>INSERT INTO #SalesSummary
It's considered a best practice to add the columns to the INSERT clause.  I know this is less code if the SELECT columns match the INSERT INTO table columns, but all it takes is one add to throw this off.

Another trick I have is to add this line of code.
-- TESTING ONLY - Set to 0 for production, 1 for extra troubleshooting info.
Declare @debug bit = 1

Open in new window

Then after major blocks of code, you can write something like this to display sets if you're in 'debug mode'.
IF @debug = 1
   begin
   SELECT 'insert for MTD Current and Prior'
   SELECT * FROM #SalesSummary 
   end

Open in new window

Not to mention add one after the big honkin' variable set query, just so you can eyeball all your variables.

Hope this helps.
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
UnifiedISCommented:
I'd look at your inner joins. Change them to left outer one at a time and see if you get some results.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
It was a join but both solutions were helpful thank you.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
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 2008

From novice to tech pro — start learning today.