|
pullum4545
Starting Member
1 Post |
 Posted - 2009-04-20 : 07:03:50
|
| I have a stored procedure that creates a #Temp table. In this #Temp table, I have a combination of fields with normal data (such as account number, title, etc) and I also have some fields that are "calculated" fields. The "calculated" fields are generated by making a call to our SQL Server Functions that produce and return the literal SQL needed to arrive at the calculated amounts. So, when my stored procedure first runs, it populates the fields that have the function calls with literal SQL that, when executed, will produce the "calculated" amount.The problem is that for normal screen use that is fine, I simply use an SQL Server Cursor to loop through the small subset of rows in the table and do an UPDATE on each of these "calculated" fields, replacing the literal SQL with the RESULT of the SQL execution.Here is an example of what the BEFORE and AFTER would look like....--------------------- BEFORE---------------------Fund Acct Title Calc1 ----------------------------------------------------------------------------------------------------------1 1 Test 1 Select .... From ... Where....1 2 Test 2 Select .... From ... Where....1 3 Test 3 Select .... From ... Where....1 4 Test 4 Select .... From ... Where....--------------------- AFTER---------------------Fund Acct Title Calc1 --------------------------------------------------------------------------------1 1 Test 1 400.00 1 2 Test 2 400.00 1 3 Test 3 400.00 1 4 Test 4 400.00 Just so you know, here is a sample of what the "calculated" functions return in each field. This is unique based on the type of calculation that is called. SELECT ISNULL(SUM(tmultidblamount),0)*-1 as TheValue from acctTransactionMain_T TM inner join acctTransactionDetail_T TD on TD.TDETAIL_TMAINiID = TM.TMAINiID inner join acctTransactionMulti_T TML on TML.TMULTI_TDETAILiID = TD.TDETAILiID inner join acctExpRevAccounts_T EX on TML.TMULTI_ERACCTiID = EX.ERACCTiID WHERE ERACCTiID = 103990 AND datepart(month,TDETAILdtLineDate) = datepart(month,getdate()) AND datepart(year,TDETAILdtLineDate) = datepart(year,getdate()) AND convert(datetime, TDETAILdtLineDate) <= convert(datetime, 'Apr 19 2009 2:26PM') AND TMAINiTransactionStatus <> 300 AND ERACCTsType = 'E' AND TMAINsType in ( 'V', 'ET', 'FT' ) Also, here is the literal SELECT SQL that I am constructing inside the stored procedure, to make the call to the SQL Server Functions.SELECT @SQL_PART_1 = 'select '+ ' ERACCTiID as ''RECID'''+ ' ,(select FUNDsFundNumber from acctFunds_T T2 where T2.FUNDiID = T1.ERACCTiFundID) as ''Field_1_Fund'' '+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion2) as ''Field_2_Account'' '+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion3) as ''Field_3_Object'' '+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion4) as ''Field_4_Cost Ctr'' '+ ' ,(select ACCTPORsNumber from acctAccountPortions_T T2 where T2.ACCTPORiID = T1.ERACCTiPortion5) as ''Field_5_Location'' '+ ' ,T1.ERACCTsAccountTitle as ''Field_6_Account Title'' '+ ' , dbo.Expenditure_F(ERACCTiID,''EXMTD'','' '','' '') as ''Field_7_Month-to-Date'' '+ ' , dbo.Expenditure_F(ERACCTiID,''EXCYTD'','' '','' '') as ''Field_8_Year-to-Date'' '+ ' , dbo.Expenditure_F(ERACCTiID,''EXFTD'','' '','' '') as ''Field_9_Fiscal-to-Date'' '+ ' , dbo.Expenditure_F(ERACCTiID,''CBUNEX'','' '','' '') as ''Field_10_Balance'' '+ ' , dbo.Expenditure_F(ERACCTiID,''EXP'','' '','' '') as ''Field_11_Pending'' ' + ' , 9999.99 as ''Field_12_Pending'' '+ ' from acctExpRevAccounts_T T1 ' |
|
Update #Temp With Result Of SQL Inside Of Field