Menu
I know this has been asked to death, and I know why SQL Server doesn't let you do it.
But is there any workaround for this, other than using Extended Stored Procedures?
And please don't tell me to convert my function into a procedure...
So what I'm really asking is: Is there ANY way to run a Stored Procedure from within a Function?
EDIT:
Point proven: there is a way around it, but it's so WRONG I wouldn't do it. I'm gonna change it to a Stored Procedure and execute it elsewhere.
689
According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks.
To execute a stored procedure or function, you only need to include its object name. Procedures and functions that are created outside of a package are called stored or standalone subprograms. Procedures and functions defined within a package are known as packaged subprograms.
Creating a Stored Procedure in SQL Server. A function can be called in a select statement as well as in a stored procedure. Since a function call would return a value we need to store the return value in a variable.
Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
EDIT: I haven't tried this, so I can't vouch for it! And you already know you shouldn't be doing this, so please don't do it. BUT...
Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx
The key bit is this bit which I have attempted to tweak for your purposes:
DECLARE @SQL varchar(500) SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec dbName..sprocName "' EXEC master..xp_cmdshell @SQL Functions are not allowed to have side-effects such as altering table contents.
Stored Procedures are.
If a function called a stored procedure, the function would become able to have side-effects.
So, sorry, but no, you can't call a stored procedure from a function.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
