I want to execute a query with concatenation of two columns so I have done this:
Id, PtName + ' ('+Investigation+')' as PtName, Y, M, D, PtCode
declare @Query nvarchar(MAX)
set @Query = 'Select Id, PtName + ''( +''Investigation''+ )'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'
Your single quotes were misplaced, they should surround the brackets
set @Query = 'Select Id, PtName + ''('' +Investigation+ '')'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'
You could debug this by using the print command: