I'm trying to execute the following script:
IF OBJECT_ID('fn.FOO') IS NOT NULL
DROP FUNCTION [fn].[FOO]
CREATE FUNCTION [fn].[FOO]
-- DO STUFF HERE
GRANT EXECUTE ON [fn].[FOO] TO [PUBLIC]
INSERT INTO [SCRIPTRUN_LOG]
The idea here is that it will attempt to drop and create a function and, if successful, add an entry into a table named SCRIPTRUN_LOG which will indicate that the function has been executed. If anything fails then the transaction gets rolled back so no entry exists in SCRIPTRUN_LOG. Unfortunately, the above example doesn't work because there is a GO statement within the transaction. However, because I'm creating a function here and the function must be the only statement in a batch I need a GO to separate batches. So I'm a little stuck here. Anyone have any ideas on how to get around this?