Hi thankyou for replying how would i be able to apply this to the example if you given me?

USE [InformationServices]

GO

/****** Object: StoredProcedure [dbo].[p18WeekPTLAdmitted] Script Date: 10/19/2009 14:53:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[p18WeekPTLAdmitted]

@status char(20),

@spec NVARCHAR(Max) as set dateformat dmy

if @status = 'ALL'

begin

select

[Hospital number]=HospNo,[NHS number]=NHSNo,[Name]=[Name],[Account number]=SpellNo,

[Referral date]=ReferDt,[Specialty code]=SpecCd,[Specialty]=Spec,[Current status]=[Event],

[Last contact date]=EventDt,[Last treatment status code]=LastTreatmentStatusCd,

[Last treatment status]=Treatment,[Consulant code]=ConsCd,[Consultant]=Cons,

[Date to be given TCI date by]=dateadd(Week,-41,BreachDt),[TCI date]=TCIDt,

[18 week breach date]=BreachDt,[Expected op]=Description+' ('+ExpOpCd+')',

127.0.0.1=IPSOTLoW,[18 week Length of wait]=(datediff(Week,ReferDt,getdate())-PLoW),

[Comments]=case when TCIDt is null then 'No TCI date' else null end,

[Wait list link error]=case when LoWdiag='888' then 'Wait list record without link' else null end,

TreatWeekNo,

[Treat in the week ending]=case when [Event]<>'WaitListSOT' then TreatWeek else null end,

[Booked assumed treatment date]=TCIDt,

[Action needed]=case when BreachDt<TCIDt and Feb='x' then 'breached booking - move' else null end,

[Site]=Site,[Wait list comment]=WLComment,[Wait list priority]=WLPriority,[26 week breach date]=[26WBreachDt],

[OPCS code]=ExpOpCd,[Age]=Age,[PCT code]=PCTCdhttp://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

from [18weekptl]

--where Spec in(SELECT Spec FROM dbo.Split3(@spec,','))

WHERE Spec in (select * from dbo.fnSplit3(@spec, ','))

and [Event] in ('WaitTimeOngoingWL','WaitListSOT')

and ClockPause<>'Yes'

order by [18 week Length of wait]

--case when TreatWeekNo=0 then 100 else TreatWeekNo end

end

regards

F