User define function with in stored procedure

Question

hi, can we create user define function with in stored procedure then end of the store procedure we need to delete that custom user define function.

Answer 1

This looks like a duplicate of:

Creating a User Defined function in Stored Procedure in SQL 2005

Answer 2

You can but it could get messy.

Look at sp_executesql. This will allow you to run arbitrary SQL, including DDL. Creating and using UDF's in this way does seem a bit dangerous -- you'll need to make sure that there aren't any name conflicts with competing threads, and there's no way to get any kind of query optimization.

I'd double check your design to make sure there isn't another solution to this!

Answer 3

Dynamic SQL is the only way.

ALTER PROC ...
AS
...
EXEC ('CREATE FUNCTION tempFunc...')
...
EXEC ('DROP FUNCTION tempFunc')
...
GO

However:

• if you have 2 concurrent executions it will fail because tempFunc already exists
• if each udf definition is different, then you need random names
• if you randomise the name, the rest of the code will have to be dynamic SQL too
• a stored proc implies reuse so just persist it
• your code will need ddl_admin or db_owner rights to create the udf
• ...

So... why do you want to do this?