2

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.

sparrow
  • 177
  • 2
  • 9

1 Answers1

2

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
JNK
  • 63,321
  • 15
  • 122
  • 138
  • Well I feel like a complete and utter dunce. I've been staring at this for well over an hour and that was the only length I hadn't checked. I had originally set it to what `ID` was in the database (10) and forgot to add the 4 when I made it longer to accommodate the double quotes and asterisks. Thank you. – sparrow Feb 28 '13 at 21:05
  • No problem, sometimes it just takes a second set of eyes. – JNK Feb 28 '13 at 21:08