I want to execute query with concatenation of two columns so I have done this
Select Id, PtName + ' ('+Investigation+')' as PtName, Y, M, D, PtCode from DiagMain
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: