SQL Server Performance

Help returning results from Stored Procedure

Discussion in 'T-SQL Performance Tuning for Developers' started by anaylor01, Sep 15, 2008.

  1. anaylor01 New Member

    I need this stored procedure to return the results of the last query. Can anyone help? I am sure it is something simple.
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    /****** Object: Stored Procedure dbo.spQ_GetASCBillingRateIDs Script Date: 9/3/2008 1:54:39 PM ******/
    alter proc spQ_GetASCBillingRateIDs2
    (
    @ScheduleID CHAR(15),
    @startdate smalldatetime,
    @enddate smalldatetime
    )
    as
    set nocount on
    truncate table tbltmpgroup
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbltmptbltest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tbltmptbltest]
    exec sp_CreateTblTmpGroup
    insert into tbltmpgroup
    SELECT DISTINCT
    case when pd.billparent = 'N' then org.eligibleorgid
    else isnull(af.parentid, org.eligibleorgid) end as billorgid,
    pd.individualbill , pd.cobrabill, pd.billparent,
    org.eligibleorgid, org.polid, org.orgpolicyid,
    pp.planid, pp.rateid,
    ps.ascinvoicedate,
    case when ps.ascclaimfromdate > @startdate then ps.ascclaimfromdate
    else @startdate end as premiumrundayFrom,
    case when ps.ascclaimtodate < @enddate then ps.ascclaimtodate
    else @enddate end as premiumrundayTo,
    fts.effdate, fts.termdate,
    case when fts.effdate > @startdate then fts.EffDate
    else @startdate end as ascStartDate,
    case when fts.termdate < @enddate then fts.termdate
    else @enddate end as ascEndDate
    FROM premiumschedule ps (nolock)
    inner join orgpolicy org (nolock)
    on org.ascinvoicerungroup between ps.premiumrundayfrom and ps.premiumrundayto
    inner join FundingTypeStatus fts
    on fts.orgpolicyid = org.orgpolicyid
    and fts.fundtype = 'ASC'
    and ((fts.effdate between @startdate and @enddate)
    or (fts.termdate between @startdate and @enddate)
    or (fts.effdate < @startdate and fts.termdate > @enddate))
    inner join eligibilityorg o (nolock)
    on org.eligibleorgid = o.eligibleorgid
    inner join policydef pd (nolock)
    on pd.polid = org.polid
    inner join policyplans pp (nolock)
    on pp.polid = org.polid
    inner join program p (nolock)
    on pd.programid = p.programid
    left join orgaffiliation af with (nolock)
    on org.eligibleorgid = af.childid
    WHERE ps.premiumscheduleid = @ScheduleID
    AND org.orgpolicyid <> ''
    go
    SELECT DISTINCT z.rateid, e.enrollid, z.ascstartdate, z.ascenddate
    into tbltmptbltest FROM enrollment E (nolock)
    inner join tbltmpgroup z
    on e.rateid = z.rateid
    go
    CREATE UNIQUE CLUSTERED INDEX IDXTempTable ON tbltmptbltest(enrollid)
    create index IDXTemptableDates on tbltmptbltest(ascstartdate,ascenddate)
    go
    select distinct t.*
    from tbltmpgroup t
    where rateid in (
    select distinct t.rateid from VW_ASC_Billing)
    order by billorgid
    set nocount off

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
  2. Adriaan New Member

    Your definition of the stored procedure actually ends at the first GO after ALTER PROC.
    The GO instruction separates different sections of a batch script, and cannot be used inside a stored procedure. Use it sparingly, and remember that you can have multiple query statements one after the other, without special keywords inbetween.
    It will even cause problems if you have a comment section with a line saying just the word "go".
    The code after the first GO after ALTER PROC will simply be executed once after the ALTER PROC instruction, but this code will not be included in the stored procedure.

Share This Page