Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can this code be in a stored proc? Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 10:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:38 AM
Points: 1,388, Visits: 1,812
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
Post #1353599
Posted Monday, September 3, 2012 10:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 42,213, Visits: 38,946
What errors do you get when you run the CREATE PROCEDURE?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1353606
Posted Monday, September 3, 2012 11:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:38 AM
Points: 1,388, Visits: 1,812
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
Post #1353609
Posted Monday, September 3, 2012 11:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 42,213, Visits: 38,946
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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1353611
Posted Monday, September 3, 2012 1:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:38 AM
Points: 1,388, Visits: 1,812
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
Post #1353623
Posted Tuesday, September 4, 2012 3:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, June 17, 2015 10:35 AM
Points: 478, Visits: 3,704
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.
Post #1354216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse