Dynamic SQL vs. Static SQL Part 2, Code

  • skumari

    SSC Rookie

    Points: 38

    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,

    @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

    The application has many concurrent users, please advice something in this case that would make it process faster.

    Regards,

    Soni

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply