SQL Server: can a stored proc that updates tables be called from within a CLR UDF

Question

I don't know why, but CLR User Defined Functions are not allowed to update tables.

Is it possible to work around this restriction by calling a stored procedure from the CLR UDF that updates tables for it ?

Answer 1

It is not CLR UDF, it is any RDBMS UDF function, by definition, cannot change the state of a database, i.e. engage:

• DELETE, INSERT, UPDATE (i.e. DML) statements,
• calls to stored procedure,
• permanently change the value of server environment variable,
• etc.
  as well as use calls to nondeterministic (with the same input returning different results) functions (like GETDATE(), NEWID(), etc.)

Update:
Oops, SQL Server 2008 relaxed the restrictions on use of non-deterministic functions. If UDF uses non-deterministic, then it is treated as non-deterministic.

One can check it by:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.FunctionName'),'IsDeterministic')