CODEWITHSUNDEEP
sqlsql-server-2005
Rajagopalk
Rajagopalk

Reputation: 31

User define function with in stored procedure

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

Answers (2)

gbn
gbn

Reputation: 432722

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?

Upvotes: 0

roufamatic
roufamatic

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

Related Questions