Hi Ryan,
I have many procedures using dynamic sqls. And my main problem comes when the IN clause is used. A.ClOffCd IN (' + @ClOffCd + ') AND A.ClCustCd IN (' + @ClCustCd +')
I am getting the values from table without quotes for each column value.
Will using the function won't impact the performance.
ALTER Procedure [dbo].[Usp_CallStatus_GetCount]
(
@CallFromDate Varchar(15) = null,
@CallToDate Varchar(15) = null,
@CompCode Varchar(6) = null,
@user-id 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=''' + @user-id + '''
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
The application has many concurrent users, please advice something in this case that would make it process faster.
Regards,
Soni