Creating multiple UDFs in one batch - SQL Server

Question

I'm asking this question for SQL Server 2008 R2

I'd like to know if there is a way to create multiple functions in a single batch statement.

I've made the following code as an example; suppose I want to take a character string and rearrange its letters in alphabetical order. So, 'Hello' would become 'eHllo'

CREATE FUNCTION char_split (@string varchar(max))
RETURNS @characters TABLE 
(
chars varchar(2)
)
AS
BEGIN

    DECLARE @length int,
            @K      int

    SET @length = len(@string)
    SET @K = 1

    WHILE @K < @length+1
        BEGIN
            INSERT INTO @characters
            SELECT SUBSTRING(@string,@K,1)
            SET @K = @K+1
        END

    RETURN
END


CREATE FUNCTION rearrange (@string varchar(max))
RETURNS varchar(max)
AS
BEGIN

    DECLARE @SplitData TABLE  (
    chars varchar(2)
    )

    INSERT INTO @SplitData SELECT * FROM char_split(@string)

    DECLARE @Output varchar(max)

    SELECT @Output = coalesce(@Output,' ') + cast(chars as varchar(10)) 
    from @SplitData
    order by chars asc

    RETURN @Output
END

declare @string varchar(max)
set @string = 'Hello'
select dbo.rearrange(@string)

When I try running this code, I get this error:

 'CREATE FUNCTION' must be the first statement in a query batch.

I tried enclosing each function in a BEGIN END block, but no luck. Any advice?

Answer 1

Just use a GO statement between the definition of the UDFs

Answer 2

Not doable. SImple like that.

YOu can make it is one statement using a GO between them.

But as the GO is a batch delimiter.... this means you send multiple batches, which is explicitly NOT Wanted in your question.

So, no - it is not possible to do that in one batch as the error clearly indicates.