Immediate not found error when running this procedure. Please help!

[vipinhcl]

Hi Sybase guys,

Please solve my problem. I am creating a procedure where using a dynamic select query. Procedure is like this

-----------------------------
Create or Replace Procedure Test_proc3(
    @Zip_Code varchar(12),
    @CollectiondateFROM varchar(12),
    @CollectiondateTo varchar(12),
    @RatePlan varchar(10),
    @avgFlag integer,
    @ProductType varchar(10),
    @drms varchar(10),
    @incomeRange integer ) AS
   
BEGIN
    DECLARE @MaxActualUsage real
    DECLARE @MaxNormalUsage real
    DECLARE @MaxPriceInCent real 
    DECLARE @MinActualUsage real 
    DECLARE @MinNormalUsage real
    DECLARE @MinPriceInCent real
    DECLARE @select_query varchar(5000)
    DECLARE @where_clause varchar(5000) 
    DECLARE @group_select varchar(1000)
DECLARE @newrange varchar(5)
   
    DROP TABLE DBA.tempData4max
    BEGIN
       
set @newrange = @incomeRange
IF @avgFlag = 0
            --SUM
            set @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart(hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=sum(Usage),NormalUsage=Sum(Normal_usage),PriceInCent=SUM(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+'''=''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'

        Else
            -- AVERAGE
            set  @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart (hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=AVG(Usage),NormalUsage=AVG(Normal_usage),PriceInCent=AVG(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+''' = ''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'

        IF @ProductType IS NOT NULL
       set @where_clause = @where_clause + ' and Retail_Product = ''' + @ProductType +''''
       
        IF @Zip_Code IS NOT NULL
            set @where_clause = @where_clause + ' and "zip code" = case when '''+@Zip_Code+''' <> ''ALL'' then '''+@Zip_Code+''' else "zip code" end'

            set @group_select = ' group by "zip code",end_date order by end_date asc'
       
        IF @RatePlan IS NOT NULL
            set @where_clause = @where_clause + ' and "T&D Rate" = '''+ @RatePlan +'''' 
            set @group_select = ' group by "T&D Rate",end_date order by end_date asc'
   
        --MAKING RESULTANT QUERY
        set @select_query = @select_query + @where_clause + @group_select
      
        -- RUN THE QUERY
   execute immediate @select_query
--with quotes off
    END
    SELECT
        @MaxActualUsage = Max(ActualUsage),
        @MaxNormalUsage = MAX(NormalUsage),
        @MaxPriceInCent = SUM(PriceInCent),
        @MinActualUsage = Min(ActualUsage),
        @MinNormalUsage = Min(NormalUsage),
        @MinPriceInCent = Min(PriceInCent)
    FROM tempData4max
   
    UPDATE tempData4max
    SET
        MaxActualUsage = @MaxActualUsage,
        MaxNormalUsage = @MaxNormalUsage,
        MaxPriceInCent = @MaxPriceInCent,
        MinActualUsage = @MinActualUsage,
        MinNormalUsage = @MinNormalUsage,
        MinPriceInCent = @MinPriceInCent
   select * FROM tempData4max
    DROP TABLE DBA.tempData4max
END
-----------------------------------------

It is compiling successfully. but when I run this using
execute Test_Proc3 'ALL','10-11-2009','15-11-2009','R1-04',0,NULL,'NO',2

It is saying immediate not found.

I tried using with result set on but then it is saying error near result. This dynamic select statement is preparing correctly as I have made the script also which returns only select statement as string. when I executed that select statement, it is running fine.

Please help me what is going wrong with execute immediate statement.

 

[PDreyer]

Replace
execute immediate @select_query
with
execute (@select_query)