September 15, 2009 at 11:57 pm
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
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply