I have a view that lists the client id along with their diagnosis. Each client may have one, two, or three diagnosis
The diagnosis is stored in a view which has the following fields. I have to use a view here because the secondary and tertiary are stored in different tables.
clientid -- unique id for client
So client 111 may have three diagnosis. The type column determines if the diagnosis is primary, secondary, or tertiary.
This code below sets the Primary Diagnosis .
CREATE Procedure [dbo].[sp_Client_Diag_Fill]
@uniqueid_c varchar(40), -- this is passed from the form to the SP
@PrimaryDiag varchar(30) OUT, -- this is output to field in that form named PrimaryDiag
@SecondaryDiag char(30) OUT -- output to field named SecondaryDiag
select @PrimaryDiag = c.diagnosis_c -- this populates the form field called c.diagnosis_c
FROM cd.v_rpt_diagnosis c
where c.clientid_c = @uniqueid_c -- determines the client
and c.type_c = 'P' -- possible values here are 'P', 'S', 'T'
The problem I'm having is with the secondary and tertiary diagnosis. I need to pull the diagnosis field based on what the type is. So it would be based on this logic
@SecondaryDiag = Select diagnosis from cd.v_rpt_diagnosis c where type='S' and @uniqueid = @clientid
I could I suppose create three Stored Procedures to return this but can I do it in one?
I tried a CTE but it I got an error saying I couldn't set the value of the variable based on a derived view.
How can I set the @SecondaryDiag and the @TertiaryDiag with the values from diagnosis based on type.
HERE IS a result set using a select * from the view
clientid_c diagnosis_c type_c
0103712212002C0005 F10.20 P
0103712372402C000D F10.20 P
0103712372402C000D F12.20 S
0103712481702C0018 F15.20 P
How can I write a Stored Procedure that fills the variables @PrimaryDiag, @SecondaryDIag?