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:
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
RETURNS TABLE as
,convert( date, [msr_Date]) as msr_Date
,(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]
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'