John Mahoney
asked on
Setting variables in a stored procedure
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
diagnosis --VARCHAR
type --VARCHAR
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
)
AS
BEGIN
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'
END
------------------------
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
cd.v_rpt_diagnosis c.
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?
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
diagnosis --VARCHAR
type --VARCHAR
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
)
AS
BEGIN
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'
END
------------------------
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
cd.v_rpt_diagnosis c.
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?
If your first one works, the others should be identical to this. However, I noted that your SELECT statement doesn't have a terminating semicolon -- you have a comment there instead. Which the semicolon is optional before the END, it is required between statements. So, try changing the last line to "and c.type_c = 'P'; -- possible values here are 'P', 'S', 'T'", and then duplicate the statement two more times for the other two variables and type_c fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Vitor's solution gave me some good ideas going forward but for some reason populated only one of the variables, the last one. Matt's solution worked. Thank you both for your help I appreciate it much.
but for some reason populated only one of the variables, the last oneThat's because a variable can store only a single value and the last row that returned from the SELECT should be a Tertiary type.
If you want to have all three variables populated with a value, then Matt's solution should be used even you can use an UNION ALL clause to join all SELECTs in a single one.