can this code be in a stored proc?

  • 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

  • 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
  • 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

  • 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
  • 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

  • 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