Help with Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Stored Procedure

Author

Topic

anaylor01
Starting Member

28 Posts

Posted - 2008-09-15 : 17:24:30

I need this SP to return the last query results. Can anyone help? Thanks.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 17:54:16

You haven't provided nearly enough information for us to help. At the very least, we'll need sample data and the expected output.

You'll need to define "last query results" for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-15 : 22:38:44

quote:
I need this SP to return the last query results

Which is ? ?

quote:

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]

Why are you not using temp table ? your stored procedure will have problem when you have more than user running it concurrently.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources