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?
Microsoft SQL ServerCSQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon