Create and use Fulltext Index in a Stored Procedure

Question

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?

Answer 1

I don't believe you can, at least not within the same proc.

Stored procs are compiled before running, so that command is going to be checked for validity before it executes.

Full text is an entirely separate executable, and while you can certainly issue SQL commands that create a new full text index, or initiate a rebuild of one, or whatever, those commands aren't going to work like normal SQL commands (they can't be within an ATOMic transaction, they can't possibly roll back, etc.).

This means that just because the CREATE FULLTEXT INDEX statement succeeds, that doesn't guarantee the index is populated and/or useful.

It's like issuing a msdb.dbo.sp_start_job to run a SQL agent job. The statement finishes quickly and returns control, but the agent could very well still be running (for a very long time).

Maybe you can trick it by calling separate stored procs, one that creates the full text index and kicks off a full build, and then another that checks the status of that build and loops till it is complete, before calling a proc that runs the query?

It seems likely to me that you will return a value faster by doing a normal WHERE Scrubbed_Account_Number LIKE '%blahblah%', even though that will do a full table scan.

In fact, I guarantee that will be faster, since it will have to do a full table scan to build the full text index.