I want to create and use a fulltext index within a single procedure. For example:
CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS --IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('Medicaid_State_Claims')) CREATE FULLTEXT INDEX ON dbo.Medicaid_State_Claims(Scrubbed_Account_Number) KEY INDEX nci_Seq_No; select * from dbo.Medicaid_State_Claims where CONTAINS(Scrubbed_Account_Number, 'blahblah');
However, this fails with the error message:
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.Medicaid_State_Claims' because it is not full-text indexed.
Uncommenting the "IF NOT EXISTS" condition makes no difference. However, if the index already exists and I uncomment that line and create the stored procedure, it works. But I'd rather not create it ahead of time, I want to create it within the stored procedure. How can I create the fulltext index and use it in the same stored procedure?