September 30, 2008 at 11:36 am
ND (9/13/2006)
you can do whatever you want in a function, update/insert/delete exec proc..etcAs long as the function returns a value.
This is absolutely not true.
SQL, like Excel and some other high-level environments makes a strong distinction between procedures and functions and does not allow its function to have side-effects. In fact it goes to considerable lengths to make it almost impossible.
[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]
September 30, 2008 at 11:37 am
Ninja's_RGR'us (9/14/2006)
As I said EXCLUDING CLR you can't call a proc in a function.
Even in CLR it is d*mned hard to do. I know, I've tried.
[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]
September 30, 2008 at 11:46 am
ND (9/17/2006)
one way is to use xp_cmdshell to call a batch file where the batch file contains the exec proc statement.
First of all, that is not "one way", it is pretty much "the only way" short of binary hacking SQL Server itself. And you are making a whole lot of assumptions about the server and execution context in order for this "trick" to be usable. And even then, it frequently fails.
And it is a long way from your original claim: "you can do whatever you want in a function" which is just flat-out wrong.
[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]
September 30, 2008 at 11:47 am
If you allow for using OPENQUERY, it gets a whole lot easier....
Again - just like michael mentioned - it's a bad idea. But you can do it under some circumstances, and the SP can change things, too....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 11:52 am
Matt Miller (9/30/2008)
If you allow for using OPENQUERY, it gets a whole lot easier....
That would be better, but I am not actually sure that OPENQUERY can do it Matt. Could you come up with an example that works from a function?
[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]
September 30, 2008 at 12:01 pm
All right - no problems:
Assume a stored proc like the following:
Create PROCEDURE matt
AS
BEGIN
SET NOCOUNT ON;
SELECT top 20 * into #matttbl
from Tally
select * from #matttbl
END
(not incredibly smart, but it does create a table and puts data into it. I also tested this with an ACTUAL table as well. The actual purpose here is stupid, but I'm not being inspired).
As far as I know - all you need is for the SP to return a single result or single resultset.
Now build yourself a linked Server to the local server (use the SQL Native client to do so). Mine is called BOB2 in this case.
Once you have that:
create function matt2()
returns int
as
begin
Return
(
select count(*) from openquery(bob2,'exec matt') h
)
end
It executes, inserts the rows into the secondary table, and selects on them. And the rows are still there after the function (assuming you don't use a temp table like here).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 12:21 pm
Cool. Have you tried it yet? (Its not that I doubt you :), its just that I have been surprised more than once by how thorough they were in locking down SQL Functions).
I'll probably have to wait until tonight to try it myself...
[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]
September 30, 2008 at 12:26 pm
Yup I have.
I haven't yet managed to get SYSTEM stored procs to work this way, but that's something having to do with OPENQUERY (it just keeps telling me I don't have access even though I do). I was thinking this could be kind of cute to wire out a queryable version of SP_WHO2.
And - considering this is getting around a prohibition from the devs - I wouldn't be incredibly surprised if this loophole does get closed at some point.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 12:43 pm
Matt Miller (9/30/2008)
And - considering this is getting around a prohibition from the devs - I wouldn't be incredibly surprised if this loophole does get closed at some point.
Aye, there's the rub.
The other problem with both of these tricks is that they rely on creating and using a second SQL process thus fooling SQL Server about the fact the you are executing out of a SQL Function. Unfortunately, this also fools some other important parts of SQL Server, like the DTC, so that it is very easy to deadlock yourself in a way that SQL Server cannot detect (and thus will never resolve).
[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]
September 30, 2008 at 12:54 pm
rbarryyoung (9/30/2008)
Aye, there's the rub.The other problem with both of these tricks is that they rely on creating and using a second SQL process thus fooling SQL Server about the fact the you are executing out of a SQL Function. Unfortunately, this also fools some other important parts of SQL Server, like the DTC, so that it is very easy to deadlock yourself in a way that SQL
Server cannot detect (and thus will never resolve).
Yup - it also has the side effect of "doubling" your connections, so knowing which one you might be using (vs which one the OPENQUERY might be) would be tricky, should you need to KILL one.
I've personally never really seen the need to "challenge" the prohibition against updating anything through a function, for reasons along the lines of what both you and Michael are getting at. I do however like occasionally using OPENQUERY to quickly turn SP output into a recordset which is why this idea popped up.
Of course - if you have something that DOESN't update data, coming out of a stored proc, and you don't mind if it might break during some patching process in the future, maybe this will do it for you.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 1:35 pm
Yeah, if I do have to do this, I would much rather use OPENQUERY than xp_CmdShell.
[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]
September 30, 2008 at 1:48 pm
rbarryyoung (9/30/2008)
Yeah, if I do have to do this, I would much rather use OPENQUERY than xp_CmdShell.
I have to agree with this - especially since you cannot be assured that xp_cmdshell would even be enabled. I know I have it disabled by default on all of our systems and only enable it when a vendor has specifically demonstrated that their application requires it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2009 at 5:33 am
I think, You can not execute user defined stored procedure in a function. You can execute only extended stored procedures and user defined functions.
Also, i dont think you can also use any DML statements like INSERT/UPDATE/DELETE (as stated by somebody above)
March 19, 2009 at 5:35 am
you can not execute any user defined stored procedures except extended stored procedures and functions.
Also, I dont thinn you can do any DML operation like INSERT/UPDATE/DELETE.
March 19, 2009 at 7:53 am
sakti.patra (3/19/2009)
you can not execute any user defined stored procedures except extended stored procedures and functions.Also, I dont thinn you can do any DML operation like INSERT/UPDATE/DELETE.
You can execute DML statements, but only on Table Variables, not actual tables (not even temp tables).
[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]
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply