Reputation: 31
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.
Upvotes: 0
Views: 2023
Reputation: 432722
Dynamic SQL is the only way.
ALTER PROC ...
AS
...
EXEC ('CREATE FUNCTION tempFunc...')
...
EXEC ('DROP FUNCTION tempFunc')
...
GO
However:
So... why do you want to do this?
Upvotes: 0
Reputation: 18495
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!
Upvotes: 1