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?
BEGIN TRY BEGIN TRANSACTION IF OBJECT_ID('fn.FOO') IS NOT NULL DROP FUNCTION [fn].[FOO] GO CREATE FUNCTION [fn].[FOO] ( @Value VARCHAR(4000) ) RETURNS VARCHAR(4000) WITH SCHEMABINDING AS BEGIN -- DO STUFF HERE RETURN @Value; END; GO GRANT EXECUTE ON [fn].[FOO] TO [PUBLIC] GO INSERT INTO [SCRIPTRUN_LOG] ( [NAME], [EXECUTED], [EXECUTEDDATE], [RESULTMESSAGE] ) VALUES ( 'ALTER FOO', 'Y', GETDATE(), ERROR_MESSAGE() ) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW END CATCH
From novice to tech pro — start learning today.