SP_ExecuteSQL with Parameters doesn't like Contains clause

Question

I've been tasked with modifying a stored procedure so that it goes from looking like this:

DECLARE @ID nvarchar(10)
SET @ID = '0000000001'

DECLARE @SQL nvarchar(200)
SET @SQL = 'SELECT AppN FROM Apps WHERE CONTAINS(ID, ''"*'' + @ID + ''*"'')'

EXECUTE SP_EXECUTESQL @SQL

to using the parameter list for SP_EXECUTESQL and not string concatenation. The issue is that the following doesn't appear to work:

DECLARE @CID nvarchar(10)
SET @CID = '0000000001'

DECLARE @ID2 nvarchar(14)
SET @ID2 = '"*' + @ID + '*"'

DECLARE @SQL nvarchar(200)
SET @SQL = 'SELECT AppN FROM Apps WHERE CONTAINS(ID, ID2)'

DECLARE @ParamDefinition NCHAR(300)
SET @ParamDefinition = '@ID2        nvarchar(10)'

EXECUTE SP_EXECUTESQL @SQL, @ParamDefinition, @ID2

For whatever reason, the first set of statements works fine. The second does not. I get the following error message: Syntax error near '"' in the full-text search condition '"*00000000'.

If I remove 4 characters from @ID the second set of statements also works. Clearly it has something to do with the length of either @ID or the column ID but I can't figure out what.

Answer 1

You define @ID2 as nvarchar(10) in your parameters for the dynamic SQL.

It's actually 14 characters, so you are cutting off the end of it.

This outputs the correct variable for me:

DECLARE @CID nvarchar(10)
SET @CID = '0000000001'

DECLARE @ID2 nvarchar(14)
SET @ID2 = '"*' + @CID + '*"'

DECLARE @SQL nvarchar(200)
SET @SQL = 'SELECT @ID2'

DECLARE @ParamDefinition NCHAR(300)
SET @ParamDefinition = '@ID2        nvarchar(14)'

EXECUTE SP_EXECUTESQL @SQL, @ParamDefinition, @ID2