Alter Table Filestream runs even if IF statement is false
- by Allison
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.
