select and dispaly only one record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select and dispaly only one record

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2013-09-18 : 12:53:11

Hi All.

I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.

How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?

Thanks.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 14:04:47

When the user selects a patient in the PatientComboBox, capture the patientid of the selected item. Then call another stored procedure with that patientid as the parameter to get all the PatientAppointments. Use that to populate the PatientAppointmentComboBox.

If there are no appointments, it will return no rows, so your PatientAppointmentComboBox will be empty.

Depending on what client application you are using you can also use binding between the Patient Records and PatientAppointment Records to achieve the same. If you go that route, you would load all the patients and all the appointments into the client code as two collections and then bind them to the controls.

eugz
Posting Yak Master

210 Posts

Posted - 2013-09-18 : 14:41:48

Hi James. Thanks for replay.

I have store procedure

quote:

Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id

But if in PatientAppointment table one of the patient has some record so same amount of record I have when open PatientAppointmentComboBox. I would like to display only one record for according Patient_Id. How to get that?

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 15:11:05

If you have more than one records that get displayed in the PatientAppointmentComboBox, and you want to display only one of those, which of those records do you want to display?

First, make this change to the stored procedure. That will show only one record, although you won't be able to predict which record will show up.

Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select TOP (1)
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id

If that looks like what you are trying to get, now decided which record to show by adding an "ORDER BY" clause. Suppose you had a column named AppointmentDate in the table, and you wanted to show the row which has the latest AppointmentDate. Then, the order by clause would be like shown below:.

Alter Procedure [dbo].[sp_GetPateintAppointment]
(
@Patient_Id as bigint
)
as

Select TOP (1)
PatientAppointment_Id
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.PatientAppointment
where Patient_Id = @Patient_Id
union
select
0
,FName + ' ' + LName PatientName
,Patient_Id
from dbo.v_Patient p
where Patient_Id = @Patient_Id
order by AppointmentDate DESC

But, none of what I described above may be what you are looking for. I didn't quite follow what you meant by you want to show only one record. When you have a combobox, isn't that really meant to list many appointments, with only one of those visible in the control, and the others visible when you click on it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources