looking to Convert This PROCEDURE TO Dynamic SQL

Question

Post reply

looking to Convert This PROCEDURE TO Dynamic SQL

Len-26184

SSC Enthusiast

Points: 162
More actions
January 21, 2004 at 8:24 pm

#107363

Hi I am looking to Convert This PROCEDURE TO Dynamic SQL

Basically I have two Different Enviroments Production and Test Databases

I would like to globally replace TciAspBilling(Production Database)

with Test_TciAspBilling(Test Database).

Basically I have two Databases A Billing Database and a Code Database

This Procedure runs in the Code database and References the Billing Database.

I have a Code database and Test_Code Database.

I was thinking of getting using this Function Some How in My Procedure

I would like to Have the same Code in my Production and Test Databases.

What I Dont want to do is a Search and replace. That is what i Did to move to Production.

Does Any One Have Any Ideas ?

Thanks

Declare @Dbname sysname

Declare @prefix Varchar (5)

set @Dbname= db_name()

select @prefix= aux_dbaDB.dbo.GET_PREFIX (@Dbname)

select @prefix

CREATE    FUNCTION GET_PREFIX

(@DBNAME SYSNAME)

RETURNS varchar(5)

AS

BEGIN

Declare @Prefix varchar(5)

Set @Prefix =Left(@DBNAME, 5)

IF @Prefix not in ('Test_','Demo_')

Set @Prefix = ' '

-- Declare @Dbname sysname

-- Declare @prefix Varchar (5)

-- set @Dbname= db_name()

-- select @prefix= aux_dbaDB.dbo.GET_PREFIX (@Dbname)

-- select @prefix

  Return @Prefix

END

CREATE                      PROCEDURE dbo.Bill_For_Storage

(

@ProductCode    char (5)

)

AS

/*

Len Kalman Completed 11/19/03

Billing For Storage

Creates a billing transaction for any application that have been in the database for more then 90 days

The first 90 days are free of charge

Billing transaction code = 14

The system will charge for storage beginning on the 91st day

Then bill every 30 days after

Example Use exec Bill_For_Storage 'FS1'

Uses two User Defined Functions

DTtoUnixTS = Datetime stamp to Unix Timestamp               '01/02/01    = XXXXXXX

convUnixDate= Convert Unix Datetime stamp to Datetimetamp      XXXXXX    = '01/02/01

*******************************************************************************************

*******************************************************************************************

*/

--DECLARE @ProductCode    char (5)

DECLARE @AppCode nvarchar (3)

DECLARE @TransactionNumber int

DECLARE @ClientNumber int

DECLARE @timestamp bigint

DECLARE @BillToId char (6)

DECLARE @BranchId char (4)

DECLARE @Datasource nvarchar (1)

DECLARE @Entrydate bigint

DECLARE @ServiceTypeCode nvarchar (2)

DECLARE @downLoaded bit

DECLARE @downLoadDate bigint

--DECLARE @ClientId int

DECLARE @FreeDays int

DECLARE @BillForDays int

DECLARE @LASTBILLTIME bigint

--set @ProductCode = 'FS1'

set @Entrydate= dbo.DTtoUnixTS(GETUTCDATE())   -- Converts UTC time to Unix Time

set @Datasource ='1'          -- Always Set to 1

set @ServiceTypeCode = '14'   -- Service Code for Billing

set @downLoaded= 0            -- Set Downloaded bit to 0

set @downLoadDate= null

set @FreeDays=90     -- First 90 Days Free                     90

set @BillForDays=30         -- After 90 days Bill Every 30 days       30

/*

**************************************************************************************

-- testing

--set @UnixDayLength = 1000 * 60 * 60 * 24

--set @CutOffTime = dbo.DTtoUnixTS(GETUTCDATE()) - (@FREEDAYS * @UNIXDAYLENGTH)

**************************************************************************************

*/

set NOCOUNT ON

DECLARE Log_cursor CURSOR FOR

-- ***************************************************************************************************

SELECT

    ApplicationState.AppCode,       ApplicationState.TransactionNumber, ApplicationState.ClientNumber,

    TciAspBilling.dbo.AppCodeToBillCode.BillToId,     TciAspBilling.dbo.AppCodeToBillCode.BranchId,

    Application.DateCreated

FROM       ApplicationState INNER JOIN Application ON

    ApplicationState.AppCode = Application.AppCode AND

    ApplicationState.TransactionNumber = Application.IdNumber

INNER JOIN TciAspBilling.dbo.AppCodeToBillCode ON

    ApplicationState.AppCode = TciAspBilling.dbo.AppCodeToBillCode.AppCode

AND     ApplicationState.ClientNumber = TciAspBilling.dbo.AppCodeToBillCode.ClientId   AND

     TciAspBilling.dbo.AppCodeToBillCode.ProductCode= @ProductCode AND

DATEDIFF(day,(SELECT dbo.convUnixDate(Application.DateCreated)), getutcdate()) > @FreeDays



-- ***************************************************************************************************

OPEN Log_cursor

FETCH NEXT FROM Log_cursor INTO @AppCode, @TransactionNumber, @ClientNumber, @BillToID, @BranchID, @TimeStamp



While (@@FETCH_STATUS = 0) Begin -- process a row

-- ****************************************************************************************

-- ***************************************************************************************************

   ---    If a application does not exsist= it has never been billed

     -- Generate a bill

IF not exists

(SELECT * FROM TciAspBilling.dbo.TransactionFile

  where ServicetypeCode=@ServiceTypeCode    AND    TciAspBilling.dbo.TransactionFile.ApplicationNumber = @TransactionNumber

   AND TciAspBilling.dbo.TransactionFile.BranchId    = @BranchID

   AND TciAspBilling.dbo.TransactionFile.BillToId      = @BillToID

   )

BEGIN

  --Generate a Bill into the TransactionFile

   INSERT INTO TciAspBilling.dbo.TransactionFile

   (ApplicationNumber,Datasource,Entrydate, ServiceTypeCode, downLoaded,   downLoadDate,BillToId,BranchId)

   VALUES

   (@TransactionNumber,@Datasource,@EntryDate,@ServiceTypeCode, @downLoaded, @downLoadDate,@BillToID,@BranchId)

END

   ***************************************************************************************************

-- Since it now has a billing record

-- Check if its has been 30 days since last billing activity

Else

Begin

  SELECT @LASTBILLTIME = (select max(EntryDate) FROM TciAspBilling.dbo.TransactionFile

   where ServicetypeCode=@ServiceTypeCode  AND TciAspBilling.dbo.TransactionFile.ApplicationNumber = @TransactionNumber

  AND TciAspBilling.dbo.TransactionFile.BranchId     =   @BranchID

  AND TciAspBilling.dbo.TransactionFile.BillToId        =   @BillToID

            )

  IF DATEDIFF(day,(SELECT dbo.convUnixDate(@LASTBILLTIME)),

getutcdate()) > @BillForDays

  Begin

  --Generate a Bill to bill for 30 days

    INSERT INTO TciAspBilling.dbo.TransactionFile

    (ApplicationNumber,Datasource,Entrydate, ServiceTypeCode,

      downLoaded, downLoadDate,BillToId,BranchId)

   VALUES

(@TransactionNumber,@Datasource,@EntryDate,@ServiceTypeCode, @downLoaded, @downLoadDate,@BillToID,@BranchId)

  End

End

***********************************************************************************************************

FETCH NEXT From Log_cursor INTO @AppCode, @TransactionNumber, @ClientNumber, @BillToID, @BranchID, @TimeStamp

End -- of row process loop

CLOSE Log_cursor

Deallocate Log_cursor

*********************************************************************************************************************

GO