• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 36
  • Last Modified:

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?
0
Russ Suter
Asked:
Russ Suter
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now