January 12, 2009 at 12:51 pm
Can I call Stored procedure in User defined function?
- Sarvesh
January 12, 2009 at 12:55 pm
Nope.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:58 pm
Thanks for the reply!
But can you explain this in detail?
January 12, 2009 at 1:01 pm
Not sure what details you need. You can't run procs from inside UDFs. What details can there be beyond, "can't do that"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 1:06 pm
I want to know why it is not possible to do so? There must be some logical Reason/constraint.
January 12, 2009 at 1:12 pm
Because UDFs are specifically designed to not do that. Books Online has the details of what they can and cannot do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 10:07 am
There 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.
January 13, 2009 at 10:45 am
sarveshcnakhate (1/12/2009)
I want to know why it is not possible to do so? There must be some logical Reason/constraint.
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 cannot 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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 10:48 am
Bingo. That is the same reason that makes the workaround a very bad idea. Thank you, Barry. 🙂
January 13, 2009 at 10:55 am
Right 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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 11:00 am
The 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.
January 13, 2009 at 11:04 am
Agreed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 11:19 am
RBarryYoung (1/13/2009)
And the SQL parser, optimizer and data engine rely heavily on the assumption that functions cannot 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.
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.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply