Is it possible to create SQL SERVER Function using transaction

Question

I have a simple script which basically do a lot of db operation. Now I have a requirement to upgrade the database for which I need to include a creation of function inside the same transaction.

BEGIN TRY --Start the Try Block..

    BEGIN TRANSACTION -- Start the transaction..
        -- HERE I need to add one function like
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuctionTest]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuctionTest]
GO
CREATE FUNCTION FuctionTest 
(   
    @inputVal int
)
RETURNS int
AS
BEGIN
    RETURN (Select Id from [dbo].[test] where Id=@inputVal)
END

        UPDATE MyChecking SET Amount = Amount - @Amount

            WHERE AccountNum = @AccountNum

        UPDATE MySavings SET Amount = Amount + @Amount

            WHERE AccountNum = @AccountNum

    COMMIT TRAN -- Transaction Success!

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0

        ROLLBACK TRAN --RollBack in case of Error

How will I get this job done? I don't want to use a separate script to create the function.

Answer 1

yes, use EXEC('...') like:

--list all functions named like '%test_function%', which will not find any
SELECT [name] FROM sys.objects WHERE [name] like '%test_function%'

--create a function named 'test_function1'
exec ('create function [dbo].[test_function1] (@x int) returns varchar(50) as BEGIN return convert(varchar(50),@x)+''!'' END')

begin transaction

--create a function 'test_function2', which will be rolled back
exec ('create function [dbo].[test_function2] (@x int) returns varchar(50) as BEGIN return convert(varchar(50),@x)+''!!!!'' END')

rollback

--show the functions named like '%test_function%'
--which will only find 'test_function1', since 'test_function2'
--was rolled back
SELECT [name] FROM sys.objects WHERE [name] like '%test_function%'

OUTPUT:

name
-----------------------

(0 row(s) affected)

name
-------------------------
test_function1

(1 row(s) affected)

EDIT to show a begin try block....

BEGIN TRY

    --list all functions named like '%test_function%', which will not find any
    SELECT [name] FROM sys.objects WHERE [name] like '%test_function%'

    --create a function named 'test_function1'
    exec ('create function [dbo].[test_function1] (@x int) returns varchar(50) as BEGIN return convert(varchar(50),@x)+''!'' END')

    begin transaction

    --create a function 'test_function2', which will be rolled back
    exec ('create function [dbo].[test_function2] (@x int) returns varchar(50) as BEGIN return convert(varchar(50),@x)+''!!!!'' END')

    RAISERROR('force catch!',16,1) --send control to the BEGIN CATCH block

    --should never get here
    COMMIT 

END TRY
BEGIN CATCH

    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END
    PRINT CASE WHEN ERROR_NUMBER() IS NOT NULL THEN 'Msg '+CONVERT(varchar(30),ERROR_NUMBER()) ELSE '' END+CASE WHEN ERROR_SEVERITY() IS NOT NULL THEN ', Level '+CONVERT(varchar(30),ERROR_SEVERITY()) ELSE '' END+CASE WHEN ERROR_STATE() IS NOT NULL THEN ', State '+CONVERT(varchar(30),ERROR_STATE()) ELSE '' END+CASE WHEN ERROR_PROCEDURE() IS NOT NULL THEN ', Procedure '+ERROR_PROCEDURE() ELSE '' END+CASE WHEN ERROR_LINE() IS NOT NULL THEN ', Line '+CONVERT(varchar(30),ERROR_LINE()) ELSE '' END+CASE WHEN ERROR_MESSAGE() IS NOT NULL THEN ', '+ERROR_MESSAGE() ELSE '' END
END CATCH


--show the functions named like '%test_function%'
--which will only find 'test_function1', since 'test_function2'
--was rolled back
SELECT [name] FROM sys.objects WHERE [name] like '%test_function%'

OUTPUT:

name
---------------------------------------------------

(0 row(s) affected)

Msg 50000, Level 16, State 1, Line 14, force catch!
name
---------------------------------------------------
test_function1

(1 row(s) affected)