July 6, 2010 at 5:11 am
Hi Please dont go with the length of SP. Its a simple one
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 SP above, the @@RowCount always seems to be > 0,even if the select statement at first returns empty.
July 6, 2010 at 5:27 am
Duplicate post. No replies please. Direct replies to http://www.sqlservercentral.com/Forums/Topic947805-1292-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply