Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


can this code be in a stored proc?


can this code be in a stored proc?

Author
Message
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47234 Visits: 44373
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

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


sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47234 Visits: 44373
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

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


sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
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
toddasd
toddasd
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 3798
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search