Post reply

Stored Procedure

  • May 15, 2010 at 11:05 am

    #222518

    ALTER PROCEDURE [dbo].[LCM_CAMPAIGNINFO]

    (

    @CAMPAIGNID VARCHAR(64),

    @LISTCOUNT INTEGER OUTPUT,

    @OPENCOUNT INTEGER OUTPUT,

    @PCBCOUNT INTEGER OUTPUT,

    @LASTSTART DATETIME OUTPUT,

    @CONTACTSCOUNT INTEGER OUTPUT,

    @CLOSEDCOUNT INTEGER OUTPUT,

    @LASTEND DATETIME OUTPUT,

    @NEVERDIALED INTEGER OUTPUT,

    @DIALOUTS INTEGER OUTPUT,

    @SUCCESSCOUNT INTEGER OUTPUT,

    @CBCOUNT INTEGER OUTPUT,

    @SUCCESSBO INTEGER OUTPUT,

    @FAILEDBO INTEGER OUTPUT,

    @VOICEOUTCOMECOUNT INTEGER OUTPUT,

    @SUCCESSVOICEOUTCOMECOUNT INTEGER OUTPUT,

    @FAILEDVOICEOUTCOMECOUNT INTEGER OUTPUT

    )

    AS

    BEGIN

    SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @OPENCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 0

    SELECT @PCBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NOT NULL

    SELECT @LASTSTART = MAX(STARTTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @CONTACTSCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @CLOSEDCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 1

    SELECT @LASTEND = MAX(ENDTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @NEVERDIALED = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND LASTOUTCOME IS NULL

    SELECT @DIALOUTS = SUM(MAXRETRIES) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @CBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NULL

    SELECT @SUCCESSBO = COUNT(OBD_AGENTOUTCOME.ContactID) FROM OBD_AGENTOUTCOME JOIN CONTACT ON CONTACT.CONTACTID = OBD_AGENTOUTCOME.ContactID AND CONTACT.STATUS = 1 JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 1 WHERE OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID

    SELECT @FAILEDBO = COUNT(ContactID) FROM OBD_AGENTOUTCOME JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 0 AND OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID

    SELECT @VOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)

    SELECT @SUCCESSVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,04)

    SELECT @FAILEDVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (01,02,03,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)

    END

    Hi friends, am completely new to sp..I just know the basic syntax of sp.. i need the above sp in a more efficient manner. Several output variables are passed but with several "select" queries. i want "case-end" to be implemented to achieve the above sp by grouping the similar conditioned queries into a single statement. can anyone please help me out??? any help would be appreciated. Thanks in advance and expecting ur reply!

    Thanks,

    Nithya S

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • May 15, 2010 at 12:10 pm

    #1167947

    Ok, I'll give you something to start with:

    The two separate statements

    SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME)

    FROM OBD_CALLACTIVITY

    WHERE CAMPAIGNID = @CAMPAIGNID

    SELECT @VOICEOUTCOMECOUNT = COUNT(CALLOUTCOME)

    FROM OBD_CALLACTIVITY

    WHERE CAMPAIGNID = @CAMPAIGNID

    AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)

    can be rewritten as

    SELECT

    @SUCCESSCOUNT = COUNT(CALLOUTCOME),

    @VOICEOUTCOMECOUNT = SUM(CASE WHEN CALLOUTCOME IS NOT NULL

    AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25) THEN 1 ELSE 0 END)

    FROM OBD_CALLACTIVITY

    WHERE CAMPAIGNID = @CAMPAIGNID

    The trick is to use SUM(CASE WHEN condition THEN 1 ELSE 0 END) as a replacement of the conditional COUNT() WHERE ... statement.

    You need to find the queries that can be combined in the way described above.

    As far as I can see you'll end up with five separate SELECT statements.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 18, 2010 at 10:37 am

    #1168949

    I would add SET NOCOUNT ON as the first line in stored procedure (before "BEGIN"):

    "setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced".

  • May 19, 2010 at 1:29 am

    #1169259

    How can i take a copy of an existing DB with a new name?? any query available for that or any other option should be used?? plz help!

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • May 19, 2010 at 8:51 am

    #1169485

    Backup existing DB and restore it as NewNameDB. You will have to use MOVE option on restore to rename database files:

    RESTORE DATABASE NewNameDB

    FROM OldDB

    WITH MOVE 'OldDB_Data' TO 'C:\MySQLServer\NewNameDB.mdf',

    MOVE 'OldDB_Log' TO 'C:\MySQLServer\NewNameDB_log.ldf';

    GO

  • May 19, 2010 at 11:05 pm

    #1169865

    Thanks a lot!! 🙂

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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