Syntax error near ',' in the full-text search condition ','

Question

This is my stored procedure. When I pass "," in @BusinessName I am getting error: Syntax error near ',' in the full-text search condition ','. How do I resolve this error?

ALTER PROCEDURE [dbo].[SearchBusiness] 
@uid bigint,
@BusinessName nvarchar(100),
@GroupId int=0
AS
BEGIN

IF ISNULL(@BusinessName,'') = '' SET @BusinessName = '""' ;

        select          
        ru.FirstName+ ' '+ru.LastName AS DisplayName,           
        ru.BusinessName
        from UserConnection uc join registereduser ru on 
        (uc.FromUserId=@uid and uc.ToUserId=ru.UserId)  
        where           
        @BusinessName = '""' OR
        contains(ru.BusinessName, @BusinessName
        ) 
END

have you comma separated businessname or only ',' in businessname? — Hardik Leuwa, Jul 11 '19 at 11:57
The datatype is nvarchar(255) and it can contain any name with or without comma. Usually the name does not contain comma. — Happy Singh, Jul 11 '19 at 12:06

Hardik Leuwa · Answer 1 · 2019-07-12T10:30:58.970

0

Try with this.

It may help you

ALTER PROCEDURE [dbo].[SearchBusiness] 
@uid bigint,
@BusinessName nvarchar(100),
@GroupId int=0
AS
BEGIN

IF ISNULL(@BusinessName,'') = '' SET @BusinessName = '""' ;

    select          
    ru.FirstName+ ' '+ru.LastName AS DisplayName,           
    ru.BusinessName
    from UserConnection uc join registereduser ru on 
    (uc.FromUserId=@uid and uc.ToUserId=ru.UserId)  
    where           
    @BusinessName = '""' OR ru.BusinessName Like '%' + lTrim(rTrim(@BusinessName)) + '%'

END

edited Jul 12 '19 at 10:30

answered Jul 11 '19 at 12:13

Hardik Leuwa

3,282
3
14
28

Thanks hardik but I want to use contains as I want a Full-Text Search. Do you know how that can be done? – Happy Singh Jul 12 '19 at 10:17
Answer is update With `LIKE` it self, using `LIKE` you can also search content in your Full-Text – Hardik Leuwa Jul 12 '19 at 10:33
Can you share your inputs to store procedure and your expected outputs? – Hardik Leuwa Jul 12 '19 at 10:39
LIKE traverses through all the data and can be slower. CONTAINS in turn uses a special text index and is thus faster than LIKE – Happy Singh Jul 12 '19 at 11:38

Syntax error near ',' in the full-text search condition ','

1 Answers1