July 6, 2010 at 4:24 am
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
July 6, 2010 at 5:33 am
@@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
July 6, 2010 at 5:38 am
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