CODEWITHSUNDEEP
sql-server-2008-r2
Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

Incorrect syntax when using sp_executesql

I have trouble executing the dynamic sql returned by the select procedure. Below is a sample of my code:

DECLARE 
    @ID int
  , @Query nvarchar(max)
SET @ID = CONVERT(int, '[P:Field_ID]')
SET @Query = 
    (
        SELECT
            FF.[External_Source_Query]
        FROM WF_Form_Field FF
            LEFT JOIN WF_Field F ON
                FF.[Field_ID] = F.[ID]
        WHERE
            FF.[External_Source_Query] IS NOT NULL
        AND F.[ID] = @ID
    )

EXEC SP_EXECUTESQL(@Query)

I am receiving an error saying Incorrect syntax near '@Query'. Any help please?

Upvotes: 0

Views: 637

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Don't use brackets:

EXEC SP_EXECUTESQL @Query

You would use brackets if you we're doing a plain EXEC:

EXEC(@Query)

However, I also fail to see how this line will ever produce a correct result:

SET @ID = CONVERT(int, '[P:Field_ID]')

Since I can see no rational way to interpret the string [P:Field_ID] as an integer.

Upvotes: 1

Related Questions