Dynamic Quer in UDF

Question

Post reply

Dynamic Quer in UDF

shailesh.thapliyal

Right there with Babe

Points: 750
More actions
September 14, 2010 at 7:46 am

#226876

Why can't we execute dynamic query inside a user defined function?
Thanks,
Shailesh

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This might help you:

http://jimbojw.com/wiki/index.php?title=SQL_2005_-_Why_You_Can%27t_EXEC%28%29_Inside_a_UDF

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

There is a very good reason that functions are not supposed to modify the data in the permanent tables. Let’s assume that for a minute that we could modify data in the function’s code. Suppose that I have a function inserts a new records into Employee table, and then select all records from that table. If I run a select statement that does a join between my function and Employees table, then one of those 2 scenarios could happen:

1) The server will use my function as the outer table in the join and will return all records from Employee table including the one that was inserted into the table from the function.

2) The server will use Employee table as the outer table and the function as the inner table and will not return the record that was inserted into the table by the function.

Since in such case the results of select query are depended on the query plan that will be used, we can’t modify data in user defined functions.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Heh... yeah... I know all the "reasons" people and MS give. I still think it's a little bit odd that I can execute dynamic SQL in a stored procedure that will do the following but I can't encapsulate it in a function for ease of programming (obviously, @table_name needs to be defined one way or the other)...

DECLARE @sql VARCHAR(200);

SELECT @sql = 'select @res = select count(*) from ' + @table_name;

EXEC(@SQL)

[rant]

Of course, I also get ticked that object names in a FROM clause can't use a variable name. If I can do it using dynamic SQL, it should be easy for the beef in SQL Server to so the same. I mean what the hay? They allow people to pass table variables now... why not scalar variables or even iTVF's for object names in FROM clauses? It would keep people from having to write a whole lot of cursors! 😉

[/rant]

--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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Jeff Moden (9/15/2010)
[rant]
Of course, I also get ticked that object names in a FROM clause can't use a variable name. If I can do it using dynamic SQL, it should be easy for the beef in SQL Server to so the same. I mean what the hay? They allow people to pass table variables now... why not scalar variables for object names in FROM clauses? 😉
[/rant]

While I feel your frustration, I wonder if it's because the compiler and optimization software would go into cardiac arrest.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Craig Farrell (9/15/2010)
Jeff Moden (9/15/2010)
[rant]
Of course, I also get ticked that object names in a FROM clause can't use a variable name. If I can do it using dynamic SQL, it should be easy for the beef in SQL Server to so the same. I mean what the hay? They allow people to pass table variables now... why not scalar variables for object names in FROM clauses? 😉
[/rant]
While I feel your frustration, I wonder if it's because the compiler and optimization software would go into cardiac arrest.

Why? They don't when you use dynamic SQL. There's no reason why the compiler and optimizer couldn't resolve such a thing. After all, they do force a recompile when you do things like mixing DDL and DML. No difference here.

--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)