• 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