Creating a User Defined function in Stored Procedure in SQL 2005

Question

Hello,

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 function and finally drop the function.

My question is that whether i can create a user defined function inside a stored procedure and drop it finally?

Thank you.

Regards NLV

Answer 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
ALTER PROCEDURE dbo.Test
AS

Declare @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.

Answer 2

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