Num records returned from query

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi Everyone,
I never noticed this before, I hope someone can help here. I have the
following query:
CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
@PatientId varchar(20),
@StartDate Datetime,
@EndDate Datetime
AS
SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
order by P.PatientID
GO

What is happening, if I run the stored procedure in Query Analyzer I will
get say 7 records for a patientid, but when the query (same proc and
parameters) is run through vb.net and SqlCommand I always get one less record
than I got in Query Analyzer. Does this make any since to you all. THanks for
any suggestions.
Michael Lee
 
Hi Everyone,
I never noticed this before, I hope someone can help here. I have the
following query:
CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
@PatientId varchar(20),
@StartDate Datetime,
@EndDate Datetime
AS
SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and P.HomelessShelter > 0
order by P.PatientID
GO

What is happening, if I run the stored procedure in Query Analyzer I will
get say 7 records for a patientid, but when the query (same proc and
parameters) is run through vb.net and SqlCommand I always get one less record
than I got in Query Analyzer. Does this make any since to you all. THanks for
any suggestions.
Michael Lee
Click to expand...

Try running the two commands while running the SQL Profiler may tell
you something. At least it will verify for you if the two commands are
indeed the same.
 
Check what values are being passed into the date parameters.

Regards,

Trevor Benedict
MCSD
 
This same thing got me the other day. When you run the query in studio
express the row count at the bottom counts *both* the rows returned in the
results panel as well as the row returned as a 'return value'. Place you
courser in the query panel and the rows at the bottom says 7, place it in the
results panel and it will say (and show) 6, place it down where the return
value shows at it will say 1.
 
Back
Top