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: Yesterday @ 3:00 PM
Points: 1,354, Visits: 1,752
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 @ 5:50 AM
Points: 40,209, Visits: 36,618
What errors do you get when you run the CREATE PROCEDURE?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, 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: Yesterday @ 3:00 PM
Points: 1,354, Visits: 1,752
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 @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, 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: Yesterday @ 3:00 PM
Points: 1,354, Visits: 1,752
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 Monday, September 3, 2012 3:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:27 PM
Points: 1,945, Visits: 3,068
What your narrative describes is a 1950's magnetic tape or punch card system, NOT RDBMS at all. You are just using SQL to do this. Your temp tables are scratch tables. The cursors are based ON the old IBM tape file routines in COBOL. Literally; we read the manuals to get ideas for the later cursor directional read syntax. You have an ORDER BY clause on the first INSERT INTO statement.

Sorting the insertion order of records on a magnetic tape makes sense, but this is SQL. The rows of a table have no ordering by definition. This is not a tricky part of the language; this is basics usually covered in the first 2-3 lessons.

I see you have a table for “Active_Jobs” and another for “Done_Jobs” but not table for “Jobs” because punch card sorters would have put cards into two decks based on a flag punched into each unit record.

You need to read ISO-11179 or any book on basic data modeling. A procedure is named “<verb>_<object>”, and the data element names are also wrong. You have verbal phrases, adjectives with a noun, etc. Think about “@last_submitted” and the obvious question “last submitted WHAT?” The rest of the code is even more vague.

I see you still write each data element on one punch card with the comma at the front; we did that so we could re-arrange the deck.

Do you really expect to get BIGINT sized data? You really get more applications than there are electrons in the Universe? You use both DECIMAL(18,0) and NUMERIC(18,0) in the parameter list; do you know the differences?

If you will post DDL, as required by minimal netiquette, maybe some can help you replace this with a real RDBMS that follows basic industry standards. I do not think this is worth kludging.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1353641
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, September 10, 2014 2:16 PM
Points: 477, Visits: 3,673
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