looking to Convert This PROCEDURE TO Dynamic SQL
-
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
-
Would this give some ideas ? http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The simplest solution to this is to not have different names on the prod and test databases.
If you're stuck with both db's on the same server/instance (for whatever reason), do use search and replace.
It will make your life easier.
The path down dynamic SQL lane has been regretted by many - it should never be anyone's first choice.
Personally, I would never go there for the above reasons.
...just my .02 of course
=;o)
/Kenneth
-
Yes, Erland's article has a section on this
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
-
Place to whole statement in your string and execute it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply