KeyWord Searches

[VBRookie]

Hello all,

I'm trying to write a stored procedure that will accept an XML string as its input and build a SQL query from it to search the database. I'm using COALESCE to leave out fields that are null. The problem is COALESCE doesn't appear to work with CONTAINS which is what I'm using for the keyword searching. Here is the meat of my SP:

BEGIN
		DECLARE @city varchar (25) 
		DECLARE @state tinyint
		DECLARE @country smallint
		DECLARE @jobType tinyint
		DECLARE @durationMonths tinyint
		DECLARE @searchKeyWords varchar (100)

		SELECT @city = tbl.city, @state = tbl.state, @country = tbl.country, @jobType = tbl.jobType, @durationMonths = tbl.durationMonths, @searchKeyWords = tbl.searchKeyWords
		FROM OPENXML (@idoc1, '/ROOT/DBData',1)
	            	WITH (city varchar (25) 'city', 
			state tinyint 'state',
			country smallint 'country',
			jobType tinyint 'jobType',
			durationMonths tinyint 'durationMonths',
			searchKeyWords varchar (100) 'searchKeyWords') AS tbl

		-- Check for Errors
		SET @errorNbr = @@ERROR
		IF @errorNbr != 0 GOTO HANDLE_ERROR

		SELECT * 
		FROM JobProfile
		WHERE city = COALESCE (@city, city)
			AND state = COALESCE (@state, state)
			AND country = COALESCE (@country, country)
			AND jobType = COALESCE (@jobType, jobType)
			AND durationMonths = COALESCE (@durationMonths, durationMonths)
			AND CONTAINS (jobDescription, COALESCE (@searchKeyWords, jobDescription))
	END

I get an error that says 'Incorrect syntax near keyword COALESCE' from the line of code that has the CONTAINS in it. Please help ...

Many kind regards,
- VB Rookie