troubleshooting Question

Setting variables in a stored procedure

Avatar of John Mahoney
John Mahoney asked on
Microsoft SQL ServerCSQL
5 Comments2 Solutions251 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Matt Bowler
Database Reliability Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros