Problem in Stored Procedure

• Shintu

  SSC Eights!

  Points: 833

  More actions

  June 23, 2010 at 7:11 am

  #223171

  Hello ! Guys

  Here is a SP that I have constructed .

  USE IRISDW_B4_FEEDRUN

  IF OBJECT_ID('Proc_PerfectJourneyReport_Technical03') IS NOT NULL

  DROP PROC Proc_PerfectJourneyReport_Technical03;

  GO

  SET ANSI_NULLS ON

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:Subhro Mukherjee

  -- Create date: 15/06/10

  -- Description: Proc_PerfectJourneyReport_Technical03

  -- =============================================

  CREATE PROC Proc_PerfectJourneyReport_Technical03

  @sortby VARCHAR(50)

  AS

  SET NOCOUNT ON;

  BEGIN

  SELECT cp.Device_Type_Code As DeviceType,COUNT(c.Container_Key) AS Journeys,

  CASE

  WHEN c.Perfect_Journey_Tech_Exception_Flag IS NULL

  OR c.Perfect_Journey_Tech_Exception_Flag = 0

  THEN COUNT(c.Container_Key) ELSE 0

  END AS PerfectJourney

  FROM

  dbo.Container c

  INNER JOIN dbo.Container_Perfect_Journey_Exceptions cp

  ON c.Container_Key = cp.Container_Key

  GROUP BY cp.Device_Type_Code,c.Perfect_Journey_Tech_Exception_Flag,

  CASE

  WHEN @sortby = 'YTD'

  THEN DATEPART(Year,getdate())

  WHEN @sortby = 'Q'+CAST(DATEPART(quarter,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)

  THEN DATEPART(quarter,getdate())

  WHEN @sortby = CAST(DATENAME(MONTH,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)

  THEN DATEPART(MONTH,getdate())

  ELSE 'Lifetime'

  END

  END

  To Execute this SP , an error is comming from this

  ERROR : Each GROUP BY expression must contain at least one column that is not an outer reference.

  I have checked a lot, the same type of stored procedure I have made before have not got any problem.

  Please help me to get the right one.

  Thanks

  Subhro

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 23, 2010 at 7:27 am

  #1183910

  The choices in your CASE resolve to constants.

  -- WHEN @sortby = 'YTD'

  GROUP BY cp.Device_Type_Code, c.Perfect_Journey_Tech_Exception_Flag, 2010

  -- WHEN @sortby = 'Q2 2010'

  GROUP BY cp.Device_Type_Code, c.Perfect_Journey_Tech_Exception_Flag, 2

  -- WHEN @sortby = 'June 2010'

  GROUP BY cp.Device_Type_Code, c.Perfect_Journey_Tech_Exception_Flag, 6

  What do you really want to GROUP BY when @sortby = 'YTD'?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden