syntax error in stored procedure when passing string containing space

Question

When I pass @param1 with space separated values, like N'here it is', there is a syntax error. If no spaces in the middle, for example N'hereitis', there is no such syntax error. Any ideas what is wrong?

I call stored procedure in this way,

EXEC @return_value = [dbo].[foo]
  @param1 = N'here it is',
  @param2 = 10,
  @param3 = 10

Error message is

message 7630, level 15, status 3, there is syntax error near 'here it is'.

 SELECT * 
 FROM (
         SELECT count(*) over() AS [Count],
                 *, ROW_NUMBER() OVER (order by t.indexfoo ASC ) AS rowNum 
         FROM   dbo.tablefoo t        
         WHERE  contains(t.col1,@param1) 
         OR     contains(t.col2,@param1) 
         OR     contains(t.col3,@param1)
      ) tt 
 WHERE  tt.rowNum between @param2 and  @param3 + @param2-1

How can this be fixed?

that won't even compile anyway, where the comma's after each param in the signature? — RPM1984, Nov 03 '10 at 04:47
HI RPM1984, what do you mean "where the comma's after each param in the signature"? Could you let me know which part of store procedure do you mean? — George2, Nov 03 '10 at 04:55
Hi Adam, I have provided how I call this store procedure. Please see my updated post. Any ideas what is wrong? — George2, Nov 03 '10 at 05:02

score 3 · Accepted Answer · edited May 23 '17 at 12:11

3

It's likely that this error is occurring at the CONTAINS operator.

SQL Server expects that multi-keywords will be separated by boolean operators (AND or OR) or surrounded in quotes.

WHERE CONTAINS(Name, ' "Mountain" OR "Road" ')

You'll have to split out each term.

For more information, see the TSQL CONTAINS() MSDN documentation, looking in particular at the Examples section -- particularly interesting might be Item J. Using CONTAINS to verify a row insertion.

To work around, I'd suggest either:

try pass in the search terms pre-formatted: ' "here" AND "it" AND "is" '. I'm unsure if this will actually work.
with your nvarchar as 'here it is', build a new string within the stored proc after split that string on the space, and build up a new string formatted as CONTAINS() requires.

edited May 23 '17 at 12:11

Community

1
1

answered Nov 03 '10 at 04:50

p.campbell

98,673
67
256
322

Thanks. So, your suggestion is I should manually separate each keyword by space? For example, change contains (NAME, N'here it is') to contains (NAME, 'here' or 'it' or 'is')? – George2 Nov 03 '10 at 04:54
1

@George: unfortunately, yes. Obviously you've got a variable, which makes sense in the real world, and unfortunately that's how `CONTAINS` works. – p.campbell Nov 03 '10 at 04:57
Thanks p.campbell! Looks like I need to find a good to use SQL split and concat string function? Could you recommend an easy to use method? I read the links you mentioned, but seems long to read. :-) – George2 Nov 03 '10 at 05:14

syntax error in stored procedure when passing string containing space

1 Answers1