Problem Understanding the SP

  • BEGIN

    SET NOCOUNT ON;

    DECLARE @Accept_Coaching int

    SET @Accept_Coaching=(SELECT

    CT.Accept_Coaching

    FROM

    ContactTracking CT

    WHERE

    CT.Accept_Coaching=1

    AND HcnID = '00000014002'

    AND CT.Contact_Date between

    (

    SELECT

    max(program_start_date)

    FROM

    programyear

    where

    getdate() between program_start_date and program_end_date

    )

    AND

    (

    select

    max(program_end_date)

    from

    programyear

    where

    getdate() between program_start_date and program_end_date

    )

    )

    IF (@@RowCount > 0)

    BEGIN

    SELECT

    Coach.LastName + ', ' + Coach.FirstName AS Coach_Name,

    Coach.Phone as Coach_Phone

    FROM

    LFLCoach.dbo.Participant

    INNER JOIN LFLCoach.dbo.CoachPartXref ON Participant.ParticipantID = CoachPartXref.ParticipantID

    INNER JOIN LFLCoach.dbo.CoachAcctXref ON CoachPartXref.CoachAcctXrefID = CoachAcctXref.CoachAcctXrefID

    INNER JOIN LFLCoach.dbo.Coach ON CoachAcctXref.CoachID = Coach.CoachID

    INNER JOIN LFLCoach.dbo.Accounts on Participant.Accountid = Accounts.Accountid

    WHERE

    Accounts.Acctid = 'pfizersd' and

    Participant.SSNum = '00000014002'

    END

    ELSE

    BEGIN

    SELECT

    '' as Coach_Name,

    '' as Coach_Phone

    END

    END

    Hi all, in the above SP,@Accept_Coaching is set according to select statement.

    The second condition starts from IF (@@RowCount > 0) .

    My problem is , even if @Accept_Coaching is 0,

    that is even if

    SET @Accept_Coaching=(SELECT

    CT.Accept_Coaching

    FROM

    ContactTracking CT

    WHERE

    CT.Accept_Coaching=1

    AND HcnID = '00000014002'

    AND CT.Contact_Date between

    (

    SELECT

    max(program_start_date)

    FROM

    programyear

    where

    getdate() between program_start_date and program_end_date

    )

    AND

    (

    select

    max(program_end_date)

    from

    programyear

    where

    getdate() between program_start_date and program_end_date

    )

    )

    returns 0.

    The condition

    BEGIN

    SELECT

    Coach.LastName + ', ' + Coach.FirstName AS Coach_Name,

    Coach.Phone as Coach_Phone

    FROM

    LFLCoach.dbo.Participant

    INNER JOIN LFLCoach.dbo.CoachPartXref ON Participant.ParticipantID = CoachPartXref.ParticipantID

    INNER JOIN LFLCoach.dbo.CoachAcctXref ON CoachPartXref.CoachAcctXrefID = CoachAcctXref.CoachAcctXrefID

    INNER JOIN LFLCoach.dbo.Coach ON CoachAcctXref.CoachID = Coach.CoachID

    INNER JOIN LFLCoach.dbo.Accounts on Participant.Accountid = Accounts.Accountid

    WHERE

    Accounts.Acctid = 'pfizersd' and

    Participant.SSNum = '00000014002'

    END

    is executed.

    But i want

    BEGIN

    SELECT

    '' as Coach_Name,

    '' as Coach_Phone

    END

    to be executed when @Accept_Coaching=0

  • @@RowCount counts the number of rows affected by the last statemen, and a SET affects no rows.

    As far as I can see, you're never using the @AcceptCoaching variable anywhere. If not, then you don't need a set at all.

    SET NOCOUNT ON;

    IF EXISTS (

    SELECT 1

    FROM ContactTracking CT

    WHERE CT.Accept_Coaching=1

    AND HcnID = '00000014002'

    AND CT.Contact_Date between (

    SELECT max(program_start_date)

    FROM programyear

    where getdate() between program_start_date and program_end_date

    )

    AND (

    SELECT max(program_end_date)

    FROM programyear

    where getdate() between program_start_date and program_end_date

    )

    )

    BEGIN

    SELECT

    Coach.LastName + ', ' + Coach.FirstName AS Coach_Name,

    Coach.Phone as Coach_Phone

    FROM

    LFLCoach.dbo.Participant

    INNER JOIN LFLCoach.dbo.CoachPartXref ON Participant.ParticipantID = CoachPartXref.ParticipantID

    INNER JOIN LFLCoach.dbo.CoachAcctXref ON CoachPartXref.CoachAcctXrefID = CoachAcctXref.CoachAcctXrefID

    INNER JOIN LFLCoach.dbo.Coach ON CoachAcctXref.CoachID = Coach.CoachID

    INNER JOIN LFLCoach.dbo.Accounts on Participant.Accountid = Accounts.Accountid

    WHERE

    Accounts.Acctid = 'pfizersd' and

    Participant.SSNum = '00000014002'

    END

    ELSE

    BEGIN

    SELECT

    '' as Coach_Name,

    '' as Coach_Phone

    END

    END

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks a lot..

Viewing 3 posts - 1 through 2 (of 2 total)

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