Fulltext Index problems

[ecejahv]

Hi,

I am a beginner in Microsoft SQL. I want to create a fulltext index from a UNIQUE key. For that purpose I have the following code:

CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX UQ__sample_table__59FA5E80
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION

This works fine. The problem is that the unique key index name is not known in advance (I got the name from the object browser) so I made a stored procedure, basically using the system "sp_helpindex" stored procedure, to get the dynamic index name. Then, if I try the follwing code:

DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX @ui_name
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION

...it does not work (Error message "Incorrect syntax near '@ui_name' ").

I have tested to print the value of the stored procedure and it is ok (same value, "UQ__sample_table__59FA5E80") and also different casting methods. Now I start to think that the variable could not be visible within the "CREATE FULLTEXT..." command context but I am not sure. Can anybody help me ?

Thanks in advance and best regards,

ecejahv

[ed_ward_graham]

I'm a bit of a novice myself but have you tried

DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
DECLARE @SqlCmd NVARCHAR(MAX)
SET @SqlCmd =
'CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX ' + @ui_name +
		' ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION';
-- now create index as a dynamic SQL command
EXEC (@SqlCmd)

It almost certainly won't be the best way to do this but it should get around the problem of using a variable name for your index.