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

soozhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Instead of modifying the column names, maybe just return the provided value as another column in your results?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Agreed.  

Dynamic set names is generally a bad idea as any downstream process may not necessarily be able to handle changed names.  SSRS, like SSIS, requires a 'contract' between data source and destination, and changing column names would break that contract every time (unless you're using SSRS matrixes).

Maybe  contain in the set for all columns, or column names like current_month, current_month_minus_one, current_month_minus_two, etc.?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.