Testing if CLR is enabled from within a function

Question

Post reply

Testing if CLR is enabled from within a function

cmartel 20772

Ten Centuries

Points: 1233
More actions
March 5, 2014 at 1:59 pm

#396378

I just wrote my first CLR function. It is a C# rewrite of a T-SQL function that crawls the nodes of large diagram in fraction of a second instead of nearly one minute.
Since I want my application to be independent from the decisions of DBAs, I am testing the 'clr enabled' value in order to call either the CLR or the T-SQL function from within a wrapper T-SQL function with code similar to:
IF EXISTS(SELECT NULL FROM sys.configurations WHERE name = 'clr enabled' AND value = 1)
INSERT INTO @tempTable SELECT * FROM clrFastFunction
ELSE
INSERT INTO @tempTable SELECT * FROM tsqlSlowFunction
Problem is that SQL server gives the error "Execution of user code in the .NET Framework is disabled" as soon as clr is disabled, like if clrFastFunction was called anyway.
Is there a way to achieve what I want to do?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

Just encapsulate the SELECT for the INSERT in an EXEC so that it is not a parse-time issue. For example:

DECLARE @Table TABLE (IntVal INT NOT NULL)

IF (1 = 0)

BEGIN

INSERT INTO @Table (IntVal)

EXEC('

SELECT IntVal

FROM SQL#.Util_GenerateInts(1, 4, 1)

')

END

What is inside of the EXEC is not parsed unless the EXEC itself is run. I get around many parse-time issues in this manner :-).

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 2

Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

cmartel 20772 (3/5/2014)
Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".

Yeah, I missed that part the first time I read it :(. Does it need to be in a function or can it be in a Stored Proc? I just tried encapsulating the CLR function call in an Inline TVF but got the parse-time error.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

It's fugly, but have you tried wrapping your clr function in a t-sql function?

That will prevent the parser error.

I have tested it on a "hello world" function to make sure it works.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

Yes, a Multistatement TVF (as opposed to an Inline one like I had tried earlier, or even a View) seems to work.

So first this:

CREATE FUNCTION dbo.TestWrapper()

RETURNS @Temp TABLE (IntVal INT)

BEGIN

INSERT INTO @Temp (IntVal)

SELECT IntVal

FROM SQL#.Util_GenerateInts(1, 4, 1)

RETURN

END

and then this should work:

DECLARE @Table TABLE (IntVal INT NOT NULL)

IF (1 = 0)

BEGIN

INSERT INTO @Table (IntVal)

SELECT IntVal

FROM dbo.TestWrapper()

END

However, if this is called repeatedly or if the CLR function returns a lot of rows, doing a Multistatement TVF could be inefficient. In that case you would be left with the Stored Procedure option.

Hope that helps.

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 6

Tricking the T-SQL compiler is a clever solution. Better, performance penalty is insignificant. Thanks a lot!