syntax error in stored procedure when passing string containing space

When I pass @param1 with space separated values, like N'here it is', there is a syntax erro开发者_Python百科r. 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?

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.