CODEWITHSUNDEEP
sql-server
Javon Fisher
Javon Fisher

Reputation: 53

My stored procedure doesn't returns rows, but my view does

I've designed the following view and tried to use it as a stored procedure; however, my stored procedure does not return the same rows as my view.

My view returns 46 rows:

SELECT   
   NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
   SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
   SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
   SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
   SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
   SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
   SUM(Cost_Center_Dollars) AS Total, Program_Type
FROM  dbo.VDP_Master_Drug_Receipts_Log 
WHERE Date_Entered BETWEEN '10/1/2011' AND '11/1/2011'
   AND (Program_Type = 'VDP-BULK')
GROUP BY NDC_Text, Trade_Name, Vendor_Code, Program_Type
ORDER BY Trade_Name

Results

My stored procedure

GO ALTER PROCEDURE [dbo].[ShowProductByCategory](@StartDate DateTime, @EndDate DateTime, @Type Varchar(15))
AS
Return 
SELECT NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
   SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
   SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
   SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
   SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
   SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
   SUM(Cost_Center_Dollars) AS Total, Program_Type
FROM dbo.VDP_Master_Drug_Receipts_Log 
WHERE Date_Entered BETWEEN @StartDate AND @StartDate
   AND (Program_Type = 'Type')
GROUP BY NDC_Text, Trade_Name, Vendor_Code, Program_Type
ORDER BY Trade_Name

Upvotes: 0

Views: 189

Answers (3)

Adam Wenger
Adam Wenger

Reputation: 17540

Your stored procedure's WHERE clause has

WHERE Date_Entered BETWEEN @StartDate AND @StartDate

That should probably be @EndDate

There is also a different Progam_Type, which would lead to different results.

Upvotes: 1

marc_s
marc_s

Reputation: 754368

Your stored procedure is selecting a dataset - so you don't need (and must not have) a RETURN clause in there!

Try this:

ALTER PROCEDURE [dbo].[ShowProductByCategory]
    (@StartDate DateTime, @EndDate DateTime, @Type Varchar(15))
AS
  -- Return    NO RETURN HERE !!
    SELECT 
       NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
       SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
       SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
       SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
       SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
       SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
       SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
       SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
       SUM(Cost_Center_Dollars) AS Total, Program_Type
    FROM dbo.VDP_Master_Drug_Receipts_Log 
    WHERE Date_Entered BETWEEN @StartDate AND @EndDate
    AND (Program_Type = 'Type')
    GROUP BY 
        NDC_Text, Trade_Name, Vendor_Code, Program_Type
    ORDER BY 
        Trade_Name

Upvotes: 1

Igor Borisenko
Igor Borisenko

Reputation: 3866

The problem is in WHERE clause

WHERE Date_Entered BETWEEN @StartDate AND @StartDate
   AND (Program_Type = 'Type')

I think it must be

WHERE Date_Entered BETWEEN @StartDate AND @EndDate
   AND (Program_Type = @Type)

Upvotes: 0

Related Questions