• that the content for each page must be less than 8k (not really a problem as greater than 8k would really be too much!)


• XML markup is mingled with content and thus performing a search may pick up matches in the markup.

A.ClOffCd IN (' + @ClOffCd + ') AND A.ClCustCd IN (' + @ClCustCd +')    

ALTER Procedure [dbo].[Usp_CallStatus_GetCount]    
(    
@CallFromDate Varchar(15) = null,    
@CallToDate Varchar(15) = null,    
@CompCode Varchar(6) = null,    
@UserID Varchar(6) = null,    
@CityCode Varchar(8000) = null,    
@CallTypeCode VarChar(2000) = null,    
@BankCode Varchar(8000) = null,    
@Frequency Varchar(50) = null,    
@RouteCode Varchar(8000)  = null,    
@Status Varchar(50) = null,    
@SQLDateFormat Varchar(5) = null,    
@ClOffCd Varchar(8000) = null,    
@ClCustCd Varchar(8000) = null  ,  
@argClientList varchar(1000)=NULL--Added by smita w . on 24-feb-09 for filtering client rights wise    
)    
as    
Begin    
    
 Exec('SELECT A.CLCALLSTATUS, COUNT(*) [Status Count]     
 FROM CLLOG A     
 INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE )     
 INNER JOIN CALLMASTER C ON (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION)     
 LEFT OUTER JOIN (    
    SELECT T.ATMID,T.ATMLOCCD,ATMCOMPCD,ATMOFFCD,LOCDESC     
    FROM MATM T    
    INNER JOIN MLOCATION K ON T.ATMLOCCD=K.LOCCODE AND T.ATMOFFCD=K.OFFCODE AND T.ATMCITY=K.CITYCODE     
   )PQR ON ( A.CLATMID=PQR.ATMID AND A.COMPCODE=PQR.ATMCOMPCD AND A.CLOFFCD=PQR.ATMOFFCD)     
 LEFT OUTER JOIN (    
    SELECT CUSTCODE,CUSTBRCODE,CUSTCUSTOMERCODE,COMPCODE,E.OFFCODE,E.CITYCODE,E.LOCCODE,LOCDESC,     
    CUSTCUSTNAME,CALLTYPECODE     
    FROM MCUSTCUSTOMER E    
    INNER JOIN MLOCATION G     
    ON E.LOCCODE =G.LOCCODE AND E.OFFCODE=G.OFFCODE AND E.CITYCODE=G.CITYCODE     
   )ABC ON ( A.CLCUSTCD=  ABC.CUSTCODE AND A.CLCUSTBRCD=ABC.CUSTBRCODE AND A.COMPCODE=ABC.COMPCODE AND A.CLOFFCD=ABC.OFFCODE AND A.CLCUSTCUSTCD=ABC.CUSTCUSTOMERCODE)    
 INNER JOIN (    
   SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE     
   FROM MCUSTOMERBRANCH D    
   INNER JOIN MCITY F ON D.CITYCODE=F.CITYCODE    
  )P ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE)     
 LEFT OUTER JOIN (    
    SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD     
    FROM CLEXEC Q INNER JOIN MROUTE R     
    ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE    
   )X ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD  and A.CLCUSTCD=X.CLCUSTCD AND A.CLCUSTBRCD=X.CLCUSTBRCD AND A.CLOFFCD=X.CLOFFCD AND A.COMPCODE=X.COMPCODE AND A.CLGENDATE=X.CLGENDATE AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR A.CLCUSTCUSTCD IS NULL))  
  
' + @argClientList + '   
 WHERE CAST(CONVERT(DATETIME,A.CLACTDATE,' + @SQLDateFormat + ') AS CHAR(11))     
 BETWEEN CONVERT(DATETIME,''' + @CallFromDate + ''',' + @SQLDateFormat + ')      
 AND CONVERT(DATETIME,''' + @CallToDate +''',' + @SQLDateFormat + ')     
 AND A.COMPCODE = ''' + @CompCode + ''' AND B.USERID=''' + @UserID + '''     
 AND P.CITYCODE IN (' + @CityCode + ') AND     
 B.CALLNAME IN (' + @CallTypeCode + ') AND A.CLCUSTBRCD IN (' + @BankCode + ')     
 AND A.CLNATURE IN(' + @Frequency + ') AND A.CLCALLSTATUS IN ('+ @Status + ')     
 AND' +  @RouteCode + ' AND A.LOGINAUTHUSERID IS NOT NULL     
 AND A.CLCALLSTATUS <> ''CL'' AND A.ClOffCd IN (' + @ClOffCd + ') AND A.ClCustCd IN (' + @ClCustCd +')    
 GROUP BY A.CLCALLSTATUS ORDER BY A.CLCALLSTATUS')    
    
End