CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008user-defined-functions
irondogs
irondogs

Reputation: 23

loading a UDF's statements into a string variable

We have a stored procedure that uses UDFs. We need to modify the content of these UDFs on the fly. Yeah, I know, I know... Not my choice. This is exactly what we are tying to do:

  1. Read the contents of a UDF
  2. Modify the contents and create a new UDF
  3. Use the UDF in an executesql statement

Is there a way to get the contents of a udf into a varchar (or nvarchar) that I can then modify using string functions?

Upvotes: 2

Views: 184

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

select object_definition(object_id('UDFName'))

Is there a way to get the contents of a udf into a varchar (or nvarchar) that I can then modify using string functions?

declare @str nvarchar(max) 

-- get function definition to @str
select @str = object_definition(object_id('UDFName'))

-- Modify definition
set @str = replace(@str, 'CREATE FUNCTION', 'ALTER FUNCTION')

-- execute alter function
exec (@str)

Upvotes: 3

Related Questions