Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

asked on

Help witha query

Hello,

I have a function that returns a table.  It wokrs ok but now the users want the column names to include a prefix/postfix based on the parameter @Month.

So if i was to call the function like ReadControlMeasurement( 22, 3)

then each column name in the returned table should be prefixed or postfixed with a 3.  Such as:

 msr_id-3
 kli_Kliniknr-3
 pat_id-3
 

Open in new window


The function gets called many times for different months and they need some help idenitifying the columns in the different tablesets returned.

Is there any way to do this? Can i use dynamic sql here?


ALTER FUNCTION [dbo].[ReadControlMeasurement]
(
	-- Add the parameters for the function here
	@pat_id int,
	@TheMonth int
)


RETURNS TABLE as
  return
  (

    SELECT  [msr_id]
      ,M.[kli_Kliniknr]
      ,M.[pat_id]
      ,convert( date, [msr_Date]) as msr_Date 
      ,[msr_Q1Frequency]
      ,[msr_Q1Severity]
      ,[msr_Q1Total]
      ,[msr_Q2Frequency]
      ,[msr_Q2Severity]
      ,[msr_Q2Total]
      ,[msr_Q3Frequency]
      ,[msr_Q3Severity]
      ,[msr_Q3Total]
      ,[msr_Q4Frequency]
      ,[msr_Q4Severity]
      ,[msr_Q4Total]
      ,[msr_Q5Frequency]
      ,[msr_Q5Severity]
      ,[msr_Q5Total]
      ,[msr_Q6Frequency]
      ,[msr_Q6Severity]
      ,[msr_Q6Total]
      ,[msr_Q7Frequency]
      ,[msr_Q7Severity]
      ,[msr_Q7Total]
      ,[msr_Q8Frequency]
      ,[msr_Q8Severity]
      ,[msr_Q8Total]
      ,[msr_Q9Frequency]
      ,[msr_Q9Severity]
      ,[msr_Q9Total]
      ,[msr_Q10Frequency]
      ,[msr_Q10Severity]
      ,[msr_Q10Total]
      ,[msr_Q11Frequency]
      ,[msr_Q11Severity]
      ,[msr_Q11Total]
      ,[msr_Q12Severity]
      ,[msr_Q12Frequency]
      ,[msr_Q12Total]
      ,[msr_Total]
      ,[msr_Pain]

      ,(select case when max(pm.med_id) is not null then 'Ja' else '' end as [Analgetikabehandling] from [PatientMedicines] PM 
          inner join Medicines Med on PM.med_id = med.med_id 
          inner join MedicineGroups MG on med.mgp_id = MG.mgp_id 
          and (MG.mgp_ATCCode = 'N 02') 
		where PM.pat_id = M.pat_id) as  [Analgetikabehandling]

      ,case when [msr_Activation] is not null then 'Ja' else '' end as [msr_Activation]
      ,case when [msr_PhysicalActivity] is not null then 'Ja' else '' end as [msr_PhysicalActivity]
      ,case when [msr_CalmEnvironment] is not null then 'Ja' else '' end as [msr_CalmEnvironment]
      ,case when [msr_Massage] is not null then 'Ja' else '' end as [msr_Massage]
      ,case when [msr_Music] is not null then 'Ja' else '' end as [msr_Music]
      ,case when [msr_Outdoors] is not null then 'Ja' else '' end as [msr_Outdoors]
      ,case when [msr_ExtraSupportMeals] is not null then 'Ja' else '' end as [msr_ExtraSupportMeals]
      ,case when [msr_ExtraSupportAnxiety] is not null then 'Ja' else '' end as [msr_ExtraSupportAnxiety]
      ,case when [msr_ExtraSupportOther] is not null then 'Ja' else '' end as [msr_ExtraSupportOther]
      ,case when [msr_OtherActivity] is not null then 'Ja' else '' end as [msr_OtherActivity]
	   ,mc.*
      ,[msr_Approved]
      ,[msr_ApprovedDate]
  
  FROM [Measurements] M outer apply dbo.ReadPatientMedicineCounts( m.pat_id, m.msr_date ) mc
  where msr_id = dbo.GetControlMeasurement_msr_id( @pat_id, @TheMonth )  
  and msr_Approved = 'Y'
  )

Open in new window

SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America 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
ASKER CERTIFIED 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