How to use COALESCE with FullText Search

[murphyhg]

I am trying to create an advanced search passing in parameters that I want to be able to use COALESCE with FullText Search on those parameters. I do realize that if they are set to null I will get an error.
What is the proper COALESCE syntax to get this to work with FullTextSearch?

create procedure dbo.usp_AdvancedSearchArticles
	@content nvarchar(255) = NULL,
	@title nvarchar(255) = NULL,
	@description 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 @description = char(34)+ @description +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), COALESCE(@content, A.content))

 

[murphyhg]

Thanks for your help always. 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.

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'