Alter Table Filestream runs even if IF statement is false

Question

I am writing a script to update a database to add Filestream capability. The script needs to be able to be run multiple times without erroring. This is what I currently have

IF ((select count(*) from sys.columns a 
       inner join sys.objects b on a.object_id = b.object_id 
       inner join sys.default_constraints c on c.parent_object_id = a.object_id 
       where a.name = 'evidence_data' and b.name='evidence' 
          and c.name='DF__evidence_evidence_data') = 0)

begin

 ALTER TABLE evidence SET ( FILESTREAM_ON = AnalysisFSGroup )   
 ALTER TABLE evidence ALTER COLUMN id ADD ROWGUIDCOL;

end

GO

The first time I run this against the database it works fine. The second time when the if statement should be false it throws an error saying "Cannot add FILESTREAM filegroup or partition scheme since table 'evidence' has a FILESTREAM filegroup or partition scheme already." If I put a simple select into the if statement and take out the alter table filestream on line it functions correctly and does not perform the if statement. So esentially it is always running the alter table filestream on statement even if the if statement is false.

Any thoughts or suggestions would be great. Thanks.

Answer 1

It's hard to reason about your business logic without knowing the details, but I'd say if you only want to alter tables that don't have filestream data space defined, consider a condition based on the following query (if it returns NULL for a given table, it means that filestream data space (file group or partition scheme) is not defined:

select filestream_data_space_id from sys.tables