Hi All,

         I have a stored procedure which displays list of ADMITTED,QUEUED and DISCHARGED patients. The patient status is stored in a table A represents ADMITTED, Q represents QUEUED and D represents DISCHARGED. I am using a dropdown to display the ADMITTED,QUEUED and DISCHARGED patients. I need to add a new option in the dropdownas ALL which displays all i.e., ADMITTED,QUEUED and DISCHARGED patients but in a classified manner on the same page like:

                            ADMITTED
                            --------
RegNo PatientName Age
---- ----------- ---
A1 PNA 70

                            QUEUED
                            --------
RegNo PatientName Age
---- ----------- ---
D1 PNQ 53

                            DISCHARGED
                            --------
RegNo PatientName Age
---- ----------- ---
Q1 PND 45


How should i code my stored procedure for this kind of formatted output?

Thanks!

Hi Caterpillar,

I think the design is wrong. You need a single table which stores allpatients, with a secondary table that has the Patient Type (Admitted, Queued, Discharged, All). You then have a foreign key reference from the Patient Type table to the Patient table.

Like this:

PatientType Table:
PatientTypeID PatientType Description
------------- ----------- -----------
1 A Admitted
2 Q Queued
3 D Discharged
4 ALL ALL


Patient Table:

PatientID PatientTypeID ReqNo PatientName Age
--------- ------------- ----- ----------- ---
1 1 A1 PNA 70
2 3 D1 PNQ 53

Etc...

That way your stored procedure is very simple. All you need to do is pass the patient type to the stored procedure. For example, if they select QUEUED from the drop down, you pass "Q" to the stored procedure:

CREATE PROCEDURE GetPatientInfo
    @PatientType int
AS
SELECT ReqNo, PatientName, Age
FROM Patient
WHERE PatientType = @PatientType

Now, if this is not an option, then you have to choices. You can create 4 different stored procedures and call the one you want depending on what they select in the drop down. OR, you can create a single procedure with a big IF statement based on what option they select in the drop down (and in this case you'll still need to pass something to to proc).

The way I explained earlier is your best option because it is more normalized.

Hope this helps...


Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Oops, I told you incorrectly. You do not pass a "Q" to the proc but ID of the item selected in the drop down (either a 1, 2, 3, or 4), not the specific letter. Make sense?

Sorry about that.

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html