September 3, 2012 at 10:40 am
Hello,
I have written a stored proc that creates temp tables and uses a table cursor and then drops the temp tables. I am not sure if this is all valid inside a stored proc. Any inputs are highly appreciated.
code:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 09/03/2012 11:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test] @LastTransactionId Decimal(18,0),@LastSubmitted numeric(18,0)
AS
SET NOCOUNT ON
create table #temp1
(
PracticeName varchar(250) not null,
Uniqueincluster decimal(18,0) not null,
AppCount bigint not null,
ProvCount bigint not null,
BillableUnits bigint not null)
create table #temp2
(
BillingMode nvarchar(50)
, QueuedAtDate datetime
, JobUnique nvarchar(50)
, [Status] nvarchar(50)
, ProductId nvarchar(75)
, TransactionDate datetime
, TransactionID nvarchar(50) not null
, AccountId int
, SubAccountId int
, EntityName nvarchar(100)
, ApplicationDescription nvarchar(100)
, OwnerName nvarchar(100)
, TransactionType nvarchar(1)
, TransactionStatus nvarchar(10)
, BillableUnits int
)
insert into #temp1
select
act.PracticeName,
act.uniqueincluster,
act.CallCount as "App Count",
SUM(pct.CallCount) as "Prov Count",
act.CallCount - SUM(pct.CallCount) as BillableUnits
from CallClusterVARASP.dbo.ApplicationCallTally act right join CallClusterVARASP.dbo.ProviderCallTally pct on pct.UniqueInCluster = act.UniqueInCluster
where act.uniqueincluster between @LastSubmitted and @LastTransactionId
group by act.uniqueincluster, act.CallCount, act.PracticeName
having act.CallCount <> SUM(pct.CallCount)
and SUM(pct.callcount) > 0
order by 1,2
DECLARE @billableunits1 varchar(10)
DECLARE @uniqueincluster1 varchar(10)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT billableunits,uniqueincluster FROM #temp1
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert into #temp2
select distinct
''Submitted'' as BillingMode
, getdate() as QueuedAtDate
, act.jobUnique as JobUnique
, ''Queued'' as Status
, ''PT'' as ProductId
, IsNull(dj.JobSubmittedAt, aj.JobSubmittedAt) as TransactionDate
, act.UniqueInCluster as TransactionID
, IsNull(dj.AccountNumber, aj.AccountNumber) as AccountId
, au.ID as SubAccountId
, act.PracticeName as EntityName
, act.Application as ApplicationDescription
, act.PracticeName as OwnerName
, ''P'' as TransactionType
, null as TransactionStatus
,'+@billableunits1+' as BillableUnits
from ApplicationCallTally act
left outer join ActiveJobs aj on aj.UniqueInCluster = act.UniqueInCluster and aj.JobUnique = aj.JobUnique
left outer join DoneJobs dj on dj.UniqueInCluster = act.UniqueInCluster and dj.JobUnique = dj.JobUnique
left outer join EzMedCore.dbo.AccountUser au on au.AccountNumber = IsNull(dj.AccountNumber, aj.AccountNumber)
and act.AspUserID = au.ID
where act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'
--PRINT(@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1
END
CLOSE TableCursor
DEALLOCATE TableCursor
insert into HAM_SubmittedQueue
select * from #temp2
drop table #temp1
drop table #temp2
GO
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
September 3, 2012 at 10:59 am
What errors do you get when you run the CREATE PROCEDURE?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2012 at 11:30 am
Thanks for the reply Gail. . Due to non existent data in our test environments, we dont have a valid environment to use this SP. Hence I will have to use it directly in my production environment. I want to take precautions before running it in our production systems.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
September 3, 2012 at 11:55 am
Untested code in production... That's just asking for trouble.
Is that valid? Yes, as far as I can tell.
Will it work correctly and perform adequately? No idea.
Ask your DBA to transfer some relevant data down to test so that you can test this. Or use a data generator tool to mock up some data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2012 at 1:25 pm
Thanks For the reply Gail. I will test it before I move it to production.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
September 4, 2012 at 3:28 pm
The last line in the dynamic sql is highly suspect.
'where act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'
It doesn't make sense to have the variable in there. I think you mean
'where act.uniqueincluster between (select min(uniqueincluster) from #temp1) and (select max(uniqueincluster) from #temp1)'
but I can't be sure of the logic of course.
Definitely heed the warning of not trying this in production. It will not work. There are only a handful of tables here to mock up for a test, it shouldn't take long.
edit: speeling
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply