InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:38:41 GMT20http://www.sqlservercentral.com/Forums/Topic634968-145-1.aspx[quote][b]RBarryYoung (1/13/2009)[/b][hr]And the SQL parser, optimizer and data engine rely heavily on the assumption that functions can[i]not[/i] cause changes in the database while they are being invoked. Almost all of the restrictions in UDF revolve around insuring that this is the case.[/quote]And one of the reasons for that is so that the optimiser is free to pick execution plans without worrying about how many times the function will be executed.Tue, 13 Jan 2009 11:19:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxAgreed.Tue, 13 Jan 2009 11:04:07 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxThe problem I've seen is that once you start doing these kinds of "dirty tricks" that the dirty tricks eventually become seen as acceptable when really they never should be. Better to run a "tight ship" and be secure.Tue, 13 Jan 2009 11:00:44 GMTKent Waldrophttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxRight Kent. It's a cool trick, but should only be used when truly desperate.I have seen it cause such things as "undetectable deadlocks". These are really nasty because they really are deadlocks that SQL Server cannot detect. As such they will not go away until you reboot SQL Server (or you can figure out how to kill the OPENROWSET connection).Tue, 13 Jan 2009 10:55:56 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxBingo. That is the same reason that makes the workaround a very bad idea. Thank you, Barry. :)Tue, 13 Jan 2009 10:48:01 GMTKent Waldrophttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspx[quote][b]sarveshcnakhate (1/12/2009)[/b][hr]I want to know why it is not possible to do so? There must be some logical Reason/constraint.[/quote]Because like Excel and some other environments, SQL Server enforces a "strong" definition of functions (the one you should have learned in college) that a Function returns a value and has no side-effects. This is as opposed to the "C-culture" view of functions, that cannot tell the difference between a function and a subroutine, and wherein the whole point of calling functions is to cause side-effects.And the SQL parser, optimizer and data engine rely heavily on the assumption that functions can[i]not[/i] cause changes in the database while they are being invoked. Almost all of the restrictions in UDF revolve around insuring that this is the case.Tue, 13 Jan 2009 10:45:19 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxThere is a workaround in which you can use OPENROWSET to call a stored procedure from a function; however, this is in general a bad idea and should be avoided.Tue, 13 Jan 2009 10:07:26 GMTKent Waldrophttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxBecause UDFs are specifically designed to not do that. Books Online has the details of what they can and cannot do.Mon, 12 Jan 2009 13:12:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxI want to know why it is not possible to do so? There must be some logical Reason/constraint.Mon, 12 Jan 2009 13:06:08 GMTsarveshcnakhatehttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxNot sure what details you need. You can't run procs from inside UDFs. What details can there be beyond, "can't do that"?Mon, 12 Jan 2009 13:01:28 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxThanks for the reply!But can you explain this in detail?Mon, 12 Jan 2009 12:58:51 GMTsarveshcnakhatehttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxNope.Mon, 12 Jan 2009 12:55:24 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic634968-145-1.aspxCan I call Stored procedure in User defined function?- SarveshMon, 12 Jan 2009 12:51:51 GMTsarveshcnakhate