Calling CLR procedures from user defined functions

Question

Post reply

Calling CLR procedures from user defined functions

ghelyar

Old Hand

Points: 348
More actions
May 15, 2008 at 4:47 pm

#380576

I am trying to use a CLR procedure inside an import script. I thought it would be easiest to put it in a function so that I wouldn't have to complicate the import script by declaring input and output variables. However, I was confronted by the message "Only functions and extended stored procedures can be executed from within a function."

What I don't understand is why can you use extended stored procedures but not CLR procedures? I am not updating any tables inside the CLR procedure or even using a contextual connection at all.

Can someone please explain the reasoning behind this to me?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I have no idea why MS does certain things. 😛

Just curious, what does the CLR do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ghelyar Old Hand Points: 348 More actions · Answer 2

I have a library for my application's use in ASP.net (C#) for 2 versions. The library can handle the rather complex conversion already and I would rather use that to migrate the BLOBs data structure in just a few lines than re-write the entire library in T-SQL (and performance is not a big issue for this one time conversion).

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

It's probably due to the requirement that functions don't have side effects. It's hard to analyse what the CLR proc does to ensure that there is no way it can have a side effect (same with regular procedures) and so it's forbidden.

Why xps are allowed though is beyond me,

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

ghelyar (5/16/2008)

I have a library for my application's use in ASP.net (C#) for 2 versions. The library can handle the rather complex conversion already and I would rather use that to migrate the BLOBs data structure in just a few lines than re-write the entire library in T-SQL (and performance is not a big issue for this one time conversion).

Thanks... I'm always interested in how other people do things and why. I really appreciate the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Thomas SNyder-379443 SSC Enthusiast Points: 159 More actions · Answer 5

ghelyar (5/16/2008)

I have a library for my application's use in ASP.net (C#) for 2 versions. The library can handle the rather complex conversion already and I would rather use that to migrate the BLOBs data structure in just a few lines than re-write the entire library in T-SQL (and performance is not a big issue for this one time conversion).

If your not returning too much data and the shape of your return is not dynamically determined then rewrite your SQLCLR procs into functions. you can calling them within standard T-SQL functions unlike sprocs.