Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Setting variables in a stored procedure

Avatar of John Mahoney
John Mahoney asked on
Microsoft SQL ServerCSQL
5 Comments1 Solution251 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



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
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 Matt Bowler
Matt BowlerFlag of New Zealand imageDatabase Reliability Engineer

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

This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers