Incorrect syntax error near 1059 when build dynamic query?

Transact-SQL
,
1

I work on SQL server 2012 I face issue error say
incorrect syntax near 1059 when build dynamic query
I try to build query dynamic

'Series' as [Series], 'Number of DAC's (1059)' as [Number of DAC's (1059)], 'Number of Bits (153)' as [Number of Bits (153)], 'Sampling Rate (Per Second) (348)' as [Sampling Rate (Per Second) (348)], 'Data Interface (243)' as [Data Interface (243)], 'Settling Time (2190)' as [Settling Time (2190)], 'DAC Type (1060)' as [DAC Type (1060)], 'Supplied Contents (985)' as [Supplied Contents (985)], 'Utilized IC / Part (984)' as [Utilized IC / Part (984)], 'Datasheet URL' as [Datasheet URL], 'Comments' as [Comments], 'Notes' as [Notes]
[Series],[Number of DAC's (1059)],[Number of Bits (153)],[Sampling Rate (Per Second) (348)],[Data Interface (243)],[Settling Time (2190)],[DAC Type (1060)],[Supplied Contents (985)],[Utilized IC / Part (984)],[Datasheet URL],[Comments],[Notes]

statement below generate text above :slight_smile:

DECLARE @Header nvarchar(max)=( select
    substring(
        (
            Select  ', '''+ ST1.DkFeatureName +''' as ['+ ST1.DkFeatureName +']' AS [text()]
            From @sh ST1 order by DisplayOrder
           
            For XML PATH ('')
        ,TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])

so How to modify dynamic SQL above to avoid display this issue or this error ?

2

The problem is the column names and data appear to have single-quotes. The best option is to make sure the data does not have those values - the next best is to use QUOTENAME to quote the column values and name.

Declare @testString varchar(100) = 'Number of DAC''s (1059)';
 Select @testString, quotename(@testString, char(39)), quotename(@testString);

image

1 Like