Feed Nulls to FullText Search without errors

Question

Post reply

Feed Nulls to FullText Search without errors

George Murphy

SSC Veteran

Points: 290
More actions
April 22, 2010 at 9:05 am

#218154

How can I run this stored prodedure and not get this error? Null or empty full-text predicate. If I feed all 4 parameters it works. If I feed less it does not. Thanks for your help always.
create procedure dbo.usp_AdvancedSearchArticles
@content nvarchar(255) = NULL,
@title nvarchar(255) = NULL,
@summary nvarchar(255) = NULL,
@keywords nvarchar(500) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @content = char(34)+ @content +char(34);
SET @title = char(34)+ @title +char(34);
SET @summary = char(34)+ @summary +char(34);
SET @keywords = char(34)+ @keywords +char(34);
SELECT DISTINCT A.ID AS ACTICLEID, A.KEYWORDS AS KEYWORD, A.BYPASSURL, A.SUMMARY, A.TITLE,
(SELECT ART_NAME FROM DBO.WhichArtType(B.ART_TYPES)) AS ART_TYPES ,(SELECT TIER FROM dbo.whichTier(A.ID)) AS TIER,
(SELECT TIER_ID FROM dbo.whichTierID(A.ID)) AS TIER_ID, (SELECT PUBDATE FROM dbo.whichPubDate(A.ID)) AS PubDate,
(SELECT EXPDATE FROM dbo.whichExpDate(A.ID)) AS ExpDate, (SELECT ARTSTATUS FROM dbo.whichArtStatus(A.ID)) AS ArtStatus,
(SELECT URL FROM dbo.getQuickSearch((SELECT TOP 1 TIER_ID FROM dbo.whichTierID(A.ID)), (SELECT TOP 1 TIER FROM dbo.whichTier(A.ID))))+CAST(A.ID AS VARCHAR(10)) AS URL
FROM ARTICLES A INNER JOIN ARTICLEMATRIX B ON A.ID = B.ART_ID
WHERE CONTAINS ((A.CONTENT), @content)
OR CONTAINS ((A.title), @title)
OR CONTAINS ((A.summary), @summary)
OR CONTAINS ((A.keywords), @keywords)
AND (PUBDATE <= { fn NOW() }) AND (EXPDATE >= { fn NOW() }) AND (A.ARTSTATUS = 1)
ORDER BY ART_TYPES
END
EXEC usp_AdvancedSearchArticles 'Standards','Standards','Standards','Standards'
April 23, 2010 at 3:07 am

This was removed by the editor as SPAM
April 23, 2010 at 5:46 am

This was removed by the editor as SPAM
April 23, 2010 at 7:55 am

This was removed by the editor as SPAM
April 23, 2010 at 8:56 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

George Murphy SSC Veteran Points: 290 More actions · Answer 1

How would I get the rest of the fields into the select?

Thanks for your help!

George Murphy SSC Veteran Points: 290 More actions · Answer 2

Thanks for your help! When I try an execute this procedure I get this error.

exec usp_AdvancedSearchArticles 'Standards'

Msg 7630, Level 15, State 3, Procedure usp_AdvancedSearchArticles, Line 22

Syntax error near 'Standards' in the full-text search condition '""Standards""'.

George Murphy SSC Veteran Points: 290 More actions · Answer 3

The problem is when I remove the char(34) + and run code like this.

exec usp_AdvancedSearchArticles 'test hi'

I get this error

Msg 7630, Level 15, State 3, Procedure usp_AdvancedSearchArticles, Line 22

Syntax error near 'hi' in the full-text search condition 'test hi'.

George Murphy SSC Veteran Points: 290 More actions · Answer 4

Thanks for your help.

I am still getting this error on the execution of this code.

Exec usp_AdvancedSearchArticles 'Standards'

Msg 7630, Level 15, State 3, Procedure usp_AdvancedSearchArticles, Line 22

Syntax error near 'Standards' in the full-text search condition '""Standards""'.

Here is my code:

create procedure dbo.usp_AdvancedSearchArticles

@content nvarchar(255) = NULL,

@title nvarchar(255) = NULL,

@summary nvarchar(255) = NULL,

@keywords nvarchar(500) = NULL

AS

SET NOCOUNT ON;

select @content = char(34)+ COALESCE(@content, @title, @summary, @keywords) +char(34),

@title = char(34)+ COALESCE(@title, @content, @summary, @keywords) +char(34),

@summary = char(34)+ COALESCE(@summary, @content, @title, @keywords) +char(34),

@keywords = char(34)+ COALESCE(@keywords, @content, @title, @summary) +char(34)

IF (@content IS NULL AND @title IS NULL AND @summary IS NULL AND @keywords IS NULL)

BEGIN

RAISERROR('At least one search criteria is required.',16,1)

RETURN

END

BEGIN

SELECT DISTINCT A.ID AS ACTICLEID, A.KEYWORDS AS KEYWORD, A.BYPASSURL, A.SUMMARY, A.TITLE,

(SELECT ART_NAME FROM DBO.WhichArtType(B.ART_TYPES)) AS ART_TYPES ,(SELECT TIER FROM dbo.whichTier(A.ID)) AS TIER,

(SELECT TIER_ID FROM dbo.whichTierID(A.ID)) AS TIER_ID, (SELECT PUBDATE FROM dbo.whichPubDate(A.ID)) AS PubDate,

(SELECT EXPDATE FROM dbo.whichExpDate(A.ID)) AS ExpDate, (SELECT ARTSTATUS FROM dbo.whichArtStatus(A.ID)) AS ArtStatus,

(SELECT URL FROM dbo.getQuickSearch((SELECT TOP 1 TIER_ID FROM dbo.whichTierID(A.ID)), (SELECT TOP 1 TIER FROM dbo.whichTier(A.ID))))+CAST(A.ID AS VARCHAR(10)) AS URL

FROM ARTICLES A INNER JOIN ARTICLEMATRIX B ON A.ID = B.ART_ID

WHERE CONTAINS (A.CONTENT, @content)

OR CONTAINS (A.title, @title)

OR CONTAINS (A.summary, @summary)

OR CONTAINS (A.keywords, @keywords)

AND (PUBDATE <= { fn NOW() }) AND (EXPDATE >= { fn NOW() }) AND (A.ARTSTATUS = 1)

ORDER BY ART_TYPES

END