SQL Server Performance Forum – Threads Archive
Diff b/w Joins and tab1,tab2
Is there any difference in performance if we use inner joins instead of "select * from tab1, tab2" Thanks,Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
you can check the execution plans for both.
Also it is good if you use joins instead as per the standard
In cases where you really can’t find a JOIN that will work, you can fall back on that very old syntax you mentioned. But you should use JOIN syntax whenever possible: not sure if SQL Server’s optimizations are guaranteed to work when you don’t. The biggest thing that JOINs do very simply, and that will not be easy to reproduce with a comma-separated table list and a WHERE statement, is LEFT JOIN and RIGHT JOIN. Other than that, JOIN syntax helps to keep things clear: the JOIN clauses are about how you’re associating data between tables, and the WHERE statement is where you filter the data. This makes things a lot easier if you ever need to make any changes to the query. If you need to do non-standard stuff, just remember that any expression that is valid in a WHERE statement is equally valid in a JOIN statement – it doesn’t have to be all = operators. Plus of course with JOINs you can use derived tables.
quote:Originally posted by ranjitjain
you can check the execution plans for both.
Also it is good if you use joins instead as per the standard
Also it is good if you use joins instead as per the standard
execution plan doesnt show any difference when i tried for a word-around table. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
quote:Originally posted by ranjitjain
you can check the execution plans for both.
Also it is good if you use joins instead as per the standard
Also it is good if you use joins instead as per the standard
execution plan doesnt show any difference when i tried for a word-around table. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
quote:Originally posted by Adriaan
In cases where you really can’t find a JOIN that will work, you can fall back on that very old syntax you mentioned. But you should use JOIN syntax whenever possible: not sure if SQL Server’s optimizations are guaranteed to work when you don’t.
The biggest thing that JOINs do very simply, and that will not be easy to reproduce with a comma-separated table list and a WHERE statement, is LEFT JOIN and RIGHT JOIN.
Other than that, JOIN syntax helps to keep things clear: the JOIN clauses are about how you’re associating data between tables, and the WHERE statement is where you filter the data. This makes things a lot easier if you ever need to make any changes to the query.
If you need to do non-standard stuff, just remember that any expression that is valid in a WHERE statement is equally valid in a JOIN statement – it doesn’t have to be all = operators.
Plus of course with JOINs you can use derived tables.
Inarguably there is no performance significance Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
quote:Inarguably there is no performance significance
All I said was "not sure if SQL Server’s optimizations are guaranteed to work when you don’t".
What could become a problem later on is if at some point they stop supporting the join-less syntax. It happened with the old syntax for outer joins too …
With just two tables, it might be clear what does what. With more than two tables, it becomes harder to see the whole picture.
So, here’s a heartfelt recommendation: use the WHERE clause for filtering only. Use JOINs for linking tables.
Dear Adrain<br /><br />The Contents of The UDF is given Below<br /><br />/****** Object: User Defined Function dbo.IRC_fFetchAcctDetl Script Date: 10/01/2006 9:22:40 PM ******/ <br />CREATE FUNCTION dbo.IRC_fFetchAcctDetl <br />( <br /> @BrID VARCHAR(25), <br /> @PrdID VARCHAR(25), <br /> @AcctID VARCHAR(25) = NULL, <br /> @p_ValueDt DATETIME, <br /> @p_Purpose VARCHAR(25), <br /> @p_HasIntParam BIT = Null, <br /> @p_HasIntDepncy BIT = Null <br />) <br />RETURNS @P_AcctInfo TABLE <br />( <br /> BrID VARCHAR(10) NULL, PrdID VARCHAR(16), AcctID VARCHAR(16) NULL, <br /> AcctOpnAmt NUMERIC(19,4) ,AcctOpenDt Datetime,TenureDays INTEGER, <br /> TenureMonths INTEGER,TenureYears INTEGER,HasintDepdn CHAR(1), <br /> HasSplRates CHAR(1),HasIntParams CHAR(1), HasFixedIntRt CHAR(1), <br /> NxtDmdDt DATETIME, NxtAccrDt DATETIME, NxtCaptDt DATETIME, <br /> NxtIntCalcDt DATETIME, IsRaiseCurrDem CHAR(1), PrdSplRtID BIGINT, <br /> IntDev VARCHAR(5), IntDevPerc NUMERIC(12,4),AcctCyID VARCHAR(5), <br /> ODInterest NUMERIC(19,4), ExpiryDt DATETIME NULL, InstallAmt NUMERIC(15,4), <br /> LstDmdDt DATETIME, LstCaptDt DATETIME, LstAccrDt DATETIME, <br /> LstIntCalcDt DATETIME,RoutExec CHAR(1),ValueDt DATETIME, MatAmt NUMERIC(19,4), <br /> IntContinue CHAR(1), ForeClsDt DATETIME, LstCompDt DATETIME, <br /> TotOS NUMERIC(19,4), ARCStat CHAR(1), LstPenaltyCalcDt DATETIME, <br /> LimitAmt NUMERIC(19,4), AcctForeClStat CHAR(1), AcctRegulzDt DATETIME <br />) <br />BEGIN <br /> <br /> /************************************************************************** <br /> ‘FUNCTION NAME :IRC_fFetchAcctDetl <br /> ‘Parameter :BrID,PrdID,AcctID,Value Date,Purpose,HasIntParam,HasIntDepency <br /> ‘Output Parameter :TABLE TYPE(Account Details) <br /> ‘Created By :EDA <br /> ‘Created Date :15-07-2005 <br /> ‘Description :This Function will fetch the values from Account Entity Table. <br /> ‘Modified By :EDA <br /> ‘Modified Date :04-05-2006(For adding new field LimitAmt) <br /> ‘**************************************************************************/ <br /> <br /> DECLARE @m_PrdType UDVC5 <br /> SELECT @m_PrdType = PrdType FROM SAM_tProductMst WHERE PrdID = @PrdID <br /> <br /> IF @m_PrdType = ‘LON’ BEGIN <br /> IF @p_Purpose = ‘DEMAND’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID,0 As ODInterest, <br /> ExpiryDt,0 As InstallAmt, LstDmdDt, LstCaptDt, <br /> LstAccrDt, LstIntCalcDt,DmdRoutExec,LnEnt.ValueDt, <br /> 0 As MatAmt, ‘N’ As IntContinue, ForeClDt,LstCompDt, <br /> TotOS,ARCStat,LstPenaltyCalcDt, SanctionAmt, ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /> AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is NULL) <br />– AND Baln.NxtDmdDt < = @p_ValueDt <br /> AND Baln.NxtDmdDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND LnEnt.OpeningDt Is Not Null <br /> AND LnEnt.ARCStat = ‘N’ –<<EDA16FEB06 <br /> END <br /> ELSE IF @p_Purpose = ‘ACCRUAL’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID, <br /> 0 As ODInterest, ExpiryDt,0 As InstallAmt, <br /> LstDmdDt, LstCaptDt, LstAccrDt, LstIntCalcDt, <br /> DmdRoutExec,LnEnt.ValueDt, 0 As MatAmt, <br /> ‘N’ As IntContinue, ForeClDt , LstCompDt,TotOS, ARCStat, <br /> LstPenaltyCalcDt, SanctionAmt, ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /> AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND NxtAccrDt < = @p_ValueDt <br /> AND NxtAccrDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND LnEnt.OpeningDt Is Not Null <br /> AND LnEnt.ARCStat = ‘N’ –<<EDA16FEB06 <br /> END <br /> –<<<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />URPOSE ADDED BY EDA ON 12.10.2005 (FOR REVERSAL ) <br /> ELSE IF @p_Purpose = ‘REVERSAL’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID, <br /> 0 As ODInterest, ExpiryDt,0 As InstallAmt, <br /> LstDmdDt, LstCaptDt, LstAccrDt, LstIntCalcDt,DmdRoutExec, <br /> LnEnt.ValueDt, 0 As MatAmt, ‘N’ As IntContinue, ForeClDt, LstCompDt, <br /> TotOS, ARCStat, LstPenaltyCalcDt, SanctionAmt, ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /> AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is Null) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND DmdRoutExec = ‘S’ –<< FOR REVERSAL CASES <br /> AND LnEnt.OpeningDt Is Not Null <br /> AND LnEnt.ARCStat = ‘N’ –<<EDA16FEB06 <br /> END <br /> ELSE IF @p_Purpose = ‘CAPITALISE’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID, <br /> 0 As ODInterest, ExpiryDt,0 As InstallAmt, <br /> LstDmdDt, LstCaptDt, LstAccrDt, LstIntCalcDt,DmdRoutExec, <br /> LnEnt.ValueDt, 0 As MatAmt, ‘N’ As IntContinue, ForeClDt, <br /> LstCompDt,TotOS, ARCStat, LstPenaltyCalcDt, SanctionAmt,ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /&g t; AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND NxtCaptDt < = @p_ValueDt <br /> AND NxtCaptDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND LnEnt.OpeningDt Is Not Null <br /> AND LnEnt.ARCStat = ‘N’ –<<EDA16FEB06 <br /> END <br /> ELSE IF @p_Purpose = ‘REPCALC’ –<<EDA06FEB06 <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID, <br /> 0 As ODInterest, ExpiryDt,0 As InstallAmt, <br /> LstDmdDt, LstCaptDt, LstAccrDt, LstIntCalcDt,DmdRoutExec, <br /> LnEnt.ValueDt, 0 As MatAmt, ‘N’ As IntContinue, ForeClDt, LstCompDt,TotOS, ARCStat, <br /> LstPenaltyCalcDt, SanctionAmt, ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /> AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is Null) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND LnEnt.OpeningDt Is Not Null <br />– AND LnEnt.ARCStat = ‘N’ –<<EDA16FEB06 <br /> END <br /> ELSE IF @p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘REPORTS’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose = ‘ONCLOSURE’ Or <br /> @p_Purpose = ‘ONCLOSUREUPD’ Or @p_Purpose = ‘PENALCALC’ Or @p_Purpose =’PENALCALCUPD’ Or @p_Purpose = ‘ONACCRUAL’ <br /> BEGIN <br /> –<< Added by EDA on 21.09.2005(purpose – onclosureupd) <br /> INSERT INTO @P_AcctInfo <br /> SELECT LnEnt.BrID,LnEnt.PrdID,LnEnt.AcctID,LnAmt ,OpeningDt,TenureDays, <br /> TenureMonths,TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> SplRateID,IntVariance,IntVarRate,CyID, <br /> 0 As ODInterest, ExpiryDt,0 As InstallAmt, <br /> LstDmdDt, LstCaptDt, LstAccrDt, LstIntCalcDt,DmdRoutExec, <br /> LnEnt.ValueDt, 0 As MatAmt, ‘N’ As IntContinue, ForeClDt, LstCompDt,TotOS, ARCStat, <br /> LstPenaltyCalcDt, SanctionAmt ,ForeClStat, RegulDt <br /> FROM LON_tInstEnt LnEnt, LON_tAcctBaln Baln <br /> WHERE LnEnt.PrdID = @PrdID <br /> AND LnEnt.BrID = @BrID <br /> AND LnEnt.PrdID = Baln.PrdID <br /> AND LnEnt.BrID = Baln.BrID <br /> AND LnEnt.AcctID = Baln.AcctID <br /> AND (LnEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND LnEnt.AccStatus = ‘L’ <br /> AND LnEnt.Approve = ‘Y’ <br /> AND LnEnt.OpeningDt Is Not Null <br /> AND LnEnt.ARCStat = ‘N’ –<<EDA16MAR06 <br /> END <br /> END <br /> ELSE IF @m_PrdType = ‘DEP’ BEGIN <br /> IF @p_Purpose = ‘DEMAND’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc, AcctCyID, ODInterest,MatDt, <br /> IsNull(InstallAmt,0),LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec, <br /> DepEffDt As ValueDt,IsNull(MatAmt,0), IsNull(IntContinue,’N’), <br /> Null As ForeClDt, LstCompDt,TotOS, ” As ARCStat, <br /> Null As LstPenaltyCalcDt, 0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND Baln.NxtDmdDt < = @p_ValueDt <br /> AND Baln.NxtDmdDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> ELSE IF @p_Purpose = ‘ACCRUAL’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc,AcctCyID,ODInterest,MatDt, <br /> IsNull(InstallAmt,0),LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec, DepEffDt As ValueDt, <br /> IsNull(MatAmt,0), IsNull(IntContinue,’N’), <br /> Null As ForeClDt, LstCompDt,TotOS, ” As ARCStat, <br /> Null As LstPenaltyCalcDt,0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND Baln.NxtAccrDt < = @p_ValueDt <br /> AND Baln.NxtAccrDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> ELSE IF @p_Purpose = ‘CAPITALISE’ <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc,AcctCyID,ODInterest,MatDt, <br /> IsNull(InstallAmt,0), LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec, DepEffDt As ValueDt, <br /> IsNull(MatAmt,0), IsNull(IntContinue,’N’), Null As ForeClDt, <br /> LstCompDt,TotOS, ” As ARCStat, Null As LstPenaltyCalcDt, <br /> 0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND Baln.NxtCaptDt < = @p_ValueDt <br /> AND Baln.NxtCaptDt = @p_ValueDt <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> ELSE IF @p_Purpose = ‘REPCALC’ –<<EDA06FEB06 <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc,AcctCyID,ODInterest,MatDt, <br /> IsNull(InstallAmt,0), LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec, DepEffDt As ValueDt, <br /> IsNull(MatAmt,0), IsNull(IntContinue,’N’), Null As ForeClDt, <br /> LstCompDt, TotOS, ” As ARCStat, Null As LstPenaltyCalcDt, <br /> 0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> ELSE IF @p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘REPORTS’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’ Or @p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’ Or @p_Purpose = ‘ONSCR’ <br /> Or @p_Purpose = ‘ONACCRUAL’ Or @p_Purpose = ‘RDPENALTY’ Or @p_Purpose = ‘RDPENALTYUPD’ <br /> BEGIN <br /> –<< Added by EDA on 21.09.2005(purpose – onclosureupd) <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt,NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc,AcctCyID,ODInterest,MatDt, <br /> IsNull(InstallAmt,0),LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec,DepEffDt As ValueDt, <br /> IsNull(MatAmt,0), IsNull(IntContinue,’N’),Null As ForeClDt, <br /> LstCompDt,TotOS, ” As ARCStat, Null As LstPenaltyCalcDt, <br /> 0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br />– AND (HasIntParams = IsNull(@p_HasIntParam,0) OR HasintDepdn = IsNull(@p_HasIntDepncy,0)) <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> ELSE IF (@p_Purpose = ‘CLSWODINT’ Or @p_Purpose = ‘CLSWODINTUPD’) –<<EDA15MAR06 <br /> BEGIN <br /> INSERT INTO @P_AcctInfo <br /> SELECT DepEnt.BrID,DepEnt.PrdID,DepEnt.AcctID,DepEnt.DepAmt,DepDt,PerdDays,PerdMnth, <br /> PerdYrs,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt, <br /> NxtDmdDt, NxtAccrDt, NxtCaptDt, NxtIntCalcDt, IsRaiseCurrDem, <br /> PrdSplRateID,IntDev,IntDevPerc,AcctCyID,ODInterest,MatDt, <br /> IsNull(InstallAmt,0), LstDmdDt, LstCaptDt, LstAccrDt, <br /> LstIntCalcDt,” As DmdRoutExec, DepEffDt As ValueDt, <br /> IsNull(MatAmt,0), IsNull(IntContinue,’N’), Null As ForeClDt, <br /> LstCompDt,TotOS, ” As ARCStat, Null As LstPenaltyCalcDt, <br /> 0 As SanctionAmt,’N’ As ForeClStat, Null As RegulDt <br /> FROM DEP_tDepAcctMst DepEnt, DEP_tAcctBaln Baln <br /> WHERE DepEnt.PrdID = @PrdID <br /> AND DepEnt.BrID = @BrID <br /> AND DepEnt.PrdID = Baln.PrdID <br /> AND DepEnt.BrID = Baln.BrID <br /> AND DepEnt.AcctID = Baln.AcctID <br /> AND (DepEnt.AcctID = @AcctID Or @AcctID Is Null) <br /> AND DepEnt.MatDt <= @p_ValueDt <br /> AND DepEnt.AcctStat = ‘L’ <br /> AND DepEnt.Approve = ‘Y’ <br /> END <br /> END <br />RETURN <br />END <br /> <br /> <br /> <br /> <br /><br /><br />Never Give Up
Can you move this post to the other thread? By the way, that’s quite a UDF you have there.
>>Is there any difference in performance if we use inner joins instead of "select * from tab1, tab2"
Note that usage of JOIN is ANSI style and recommended Madhivanan Failing to plan is Planning to fail
Thanks Adriaan and Madhi for your heartfelt/ANSI recommendations [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>