Full Text Search column name parameter

[JohnnyLong]

Hi there,

Does anyone know how to use a parameter as a column name in a CONTAINS stored procedure, so instead of:

SELECT surname, firstname
FROM Candidates
WHERE CONTAINS(Candidates.Surname, @pSearch2)

I need to use:

SELECT surname, firstname
FROM Candidates
WHERE CONTAINS(@pSearch1, @pSearch2)

I've tried declaring @pSearch1 as varchar(50) and setting it to 'Candidates.Surname' but it doesn't like it.

Any ideas?

John

 

[JohnnyLong]

I think my answer is Dynamic SQL:

DECLARE @pSearch1 as varchar(500),
@pSearch2 as varchar(500),
@pSearch3 as varchar(500),
@pParam as varchar(20),
@pColumn as varchar(30)
SET @pColumn = 'Candidates.Surname'
SET @pParam = '"Smith*"'

SET @pSearch1 = 'SELECT Candidates.surname, firstname FROM Candidates WHERE CONTAINS(@pColumn, @pParam)'
SET @pSearch2 = REPLACE(@pSearch1, '@pColumn', @pColumn)
SET @pSearch3 = REPLACE(@pSearch2, '@pParam', @pParam)

EXEC (@pSearch3)

Anyone got any better solutions?

John