Link to home
Start Free TrialLog in
Avatar of John Mahoney
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?
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

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
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Mahoney
John Mahoney

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 one
That'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.