Why is this SQL IF condition being ignored?

Question

Have this script:

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
CREATE UNIQUE INDEX FtsKeyIndex ON Records([Id])
CREATE FULLTEXT CATALOG ftcatalog AS DEFAULT
CREATE FULLTEXT INDEX ON Records
    (
        Description Language 1033
    )   
    KEY INDEX FtsKeyIndex ON ftcatalog
    WITH STOPLIST = SYSTEM
END

Running it, I get:

Full-Text Search is not installed, or a full-text component cannot be loaded.

Running the following line on its own returns a 0:

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Have also tried:

IF 1 = (SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

And:

IF 1 = (select CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit))

Why is my IF statement getting ignored?

Answer 1

Your IF statement isn't being ignored. The error is being reported during compilation. Your code never even has a chance to execute.

You need to move any code that relies on this sort of condition into a different scope so that it gets compiled separately:

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
  EXEC sp_executesql N'CREATE UNIQUE INDEX FtsKeyIndex ON Records([Id])'
  EXEC sp_executesql N'CREATE FULLTEXT CATALOG ftcatalog AS DEFAULT'
  EXEC sp_executesql N'CREATE FULLTEXT INDEX ON Records
    (
        Description Language 1033
    )   
    KEY INDEX FtsKeyIndex ON ftcatalog
    WITH STOPLIST = SYSTEM'
END

As an analogy - in C#, you wouldn't expect to be able to write a method that, given an object of type X, first uses reflection to determine whether X has a method called Y and then, directly afterwards, includes the line X.Y();, even if it is inside an if block.