Avatar of soozh
soozh
Flag 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

Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
SOLUTION
Shaun Kline

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes