How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause