Calling dynmic sql from function

Question

Calling dynmic sql from function

arup chakraborty

Hall of Fame

Points: 3640
More actions
April 9, 2012 at 7:44 am

#271603

Hi All,
I need to do some complex calculation, where I must use dynamic sql. The problem is, I need to do everyting inside view, hence I throught I will do the calculation inside a function and then call the frunction from the view, but it seems UDF doesnot support dynamic sql.
I cannot use SP as this is a BI/BO environment.
Please help me for any workaround.
Regards,
Arup

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you are correct; dynamic SQL is not allowed in a UDF, dynamic SQL limits you to either a procedure or ad-hoc code that will need to be built and executed clientside.

what exactly are you trying to do?

There's probably a way to do what you ask without dynamic SQL.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi Lowell,

Thanks for your reply. We need to call a function from a view; the function contains complex calculation, whuch needed dynamic sql.

There are number of limitations:

1. We cant call sp from view, hence going for UDF

2. UDF doesnot support dynamic sql, which is necessory.

Is there any way to accomplish this?

Regards,

Arup

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Show us what you are trying to accomplish, show us all the code.