Creating A User Defined Function In Stored Procedure In Sql 2005

I have a stored procedure in which i want to create a user defined function - Split (splits a string separated with delimiters and returns the strings in a table), make use of the

Solution 1:

Technically...yes you could but that does not mean you should. You would have to be careful about avoiding GO statements (just use Exec for each batch) but you could do something like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE dbo.Test
ASDeclare@Sql nvarchar(max)

Set@Sql='CREATE FUNCTION dbo.Foo
(   
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT 0 As Bar
)'Exec(@Sql)

Select*From dbo.Foo()


Set@Sql='Drop Function dbo.Foo'Exec(@Sql)

Return
GO
Exec dbo.Test

That said, I would strongly recommend against this sort of solution, especially if the function you want is something that would be useful like a Split function. I would recommend just creating the UDF and using it and leaving it until you might use it again.

Solution 2:

CTEs would be good here too, depending on what your UDF is trying to do.