Execute a stored procedure inside a user defined function
-
Hello,
I need to execute a stored procedure inside a function. Can it be possible?
Thanks...
MCVilbar
-
I have seen no way to achive this. Either the procedure needs to be rewrote to be a function, or the function needs to be made into a procedure
-
you can do whatever you want in a function, update/insert/delete exec proc..etc
As long as the function returns a value.
-
Hi
dont think u can execute a procedure inside a function.
either convert both the function and procedure to procedures or convert them to functions
All The BEST
"Keep Trying"
-
Not that I don't want to believe you. But excluding CLR, how the heck are you gonna do that??
I'd like to so some working code if you could provide some exemple(s) please.
. -
I thinks you still can....though I never tested.
if you can call clr in a stored proc you can call it a function.
-
As I said EXCLUDING CLR you can't call a proc in a function.
-
depends on how you call proc inside a function, there's always a work around.
Don't just accept what it is in bol.
-
ND I'm not telling you are a liar.
SHOW US how the heck you do that!
-
one way is to use xp_cmdshell to call a batch file where the batch file contains the exec proc statement. In the function you can call the extended proc.
eg.
create function...
exec master.sys.xp_cmpshell 'C:\test.bat'
....
return...
I'm not saying it is a good way of doing so. All I'm saying is there is a way of calling proc inside a function.
-
Thanx.
Let's just call that plan B for now
. -
Can you please tell whts the alternative to call the procedure from function.
I want to call procedure - sp_executesql from function
-
Granted, there are probably ways to call xp_executeSQL from a function.
I would never recommend doing any of them. You could do this with the CLR and I am sure there is at least one other really bad idea that will work. In functions, you cannot call stored procedures, make DDL changes, or run dynamic SQL.
There are lots of good reasons for this, but a great example is to prevent a function (which can be past of a select list) from doing something that could conflict with itself if the query optimizer decides to multi-thread your query (Max Degrees of Parallelism).
Think of it like this, you have a function in your select list and it calls dynamic SQL to create a table, update some values, and then drop the table. Now, if you call this procedure and the query optimizer does not run your query processing with any parallelism, it may be fine. If it does decide to parallel process, you may have the function creating the table while another thread has the function running and the table has already been created. This would clearly cause stability issues pretty quickly.
If you are trying to do something in a function that requires dynamic SQL or the execution of a stored procedure, the appropriate thing to do is to go back and re-think your design. See if you can find a way of doing what you want to that does not conflict with the database platform you are using.
Don't feel bad about doing this - everyone comes up with a bad idea now and then. Take it as an opportunity to go back and come up with a better one.
-
I'm not sure about this, you can't generate a newid() inside a function. It's generating an error when trying to do so, yet in a sproc you can,
has anyone generated a newid() inside a finction?
thanks
-
I'm not sure about this, you can't generate a newid() inside a function. It's generating an error when trying to do so, yet in a sproc you can,
has anyone generated a newid() inside a finction?
thanks
Viewing 15 posts - 1 through 15 (of 63 total)
You must be logged in to reply to this topic. Login to reply