Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored procedure in User defined function? Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 17, 2009 1:49 PM
Points: 4, Visits: 10
Can I call Stored procedure in User defined function?

- Sarvesh
Post #634968
Posted Monday, January 12, 2009 12:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #634973
Posted Monday, January 12, 2009 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 17, 2009 1:49 PM
Points: 4, Visits: 10
Thanks for the reply!
But can you explain this in detail?
Post #634980
Posted Monday, January 12, 2009 1:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #634986
Posted Monday, January 12, 2009 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 17, 2009 1:49 PM
Points: 4, Visits: 10
I want to know why it is not possible to do so? There must be some logical Reason/constraint.
Post #634994
Posted Monday, January 12, 2009 1:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #634997
Posted Tuesday, January 13, 2009 10:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 04, 2013 4:22 PM
Points: 480, Visits: 467
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.
Post #635608
Posted Tuesday, January 13, 2009 10:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #635647
Posted Tuesday, January 13, 2009 10:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 04, 2013 4:22 PM
Points: 480, Visits: 467
Bingo. That is the same reason that makes the workaround a very bad idea. Thank you, Barry. :)
Post #635651
Posted Tuesday, January 13, 2009 10:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #635659
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse