Create a function with in a transaction

I'm trying to execute the following script:
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

Open in new window

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?
LVL 22
Russ SuterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
functions cannot be used to modify the DB.
0
Scott PletcherSenior DBACommented:
I don't think you need the transaction approach here, although it could be made to work.

Instead, condition the subsequent statements on whether or not the function exists:

CREATE FUNCTION
...
GO
IF OBJECT_ID('fn.FOO') IS NOT NULL
BEGIN
    GRANT EXECUTE ON [fn].[FOO] TO [PUBLIC];
    INSERT INTO [SCRIPTRUN_LOG]
      (
            [NAME],
            [EXECUTED],
            [EXECUTEDDATE],
            [RESULTMESSAGE]
      )
      VALUES
      (
            'ALTER FOO',
            'Y',
            GETDATE(),
            ERROR_MESSAGE()
      );
END /*IF*/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russ SuterAuthor Commented:
Yes, thanks. I like this approach. Sometimes it's good to get another perspective to be able to figure this sort of stuff out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.