Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
markusf
Starting Member
15 Posts |
 Posted - 2007-09-03 : 09:16:07
|
| HelloI need to call a function that does an StoredProc call.I get the following errormessage:Server: Msg 557, Level 16, State 2, Procedure get_test_mfo, Line 6Only functions and extended stored procedures can be executed from within a function.Is there any way I can bypass this, ie. wrapping the sp-call in a Extended StoredProc.--Markus Foss, Norway |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 16:57:27
|
| why do you need to do it this way? SPs are supposed to return values or resultsets, if you need the values and saving them inside the function, best option is to just convert the sp into function perhaps?I'm not a guru but that's how I'll do it--------------------keeping it simple... |
 |
|
|
markusf
Starting Member
15 Posts |
Posted - 2007-09-05 : 02:31:57
|
| The thing is that this will be used as an interface between two components. so what I want to do is the following.Send in enough data for the function to find the corresponding record in a fuzzy way. If the record does not exist then the storedProc will insert the record into the table. The return will be a string: "[record id]|match-category". The logic used to find the correct record will be fairly complex, and since it might involves insertion, a function can't be used--Markus Foss, Norway |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 04:14:14
|
| You can set up a database connection within a function and use that to run some dynamic SQL, but its such a ghastly kludge I'm sure Microsoft never intended that it should be used!Move the code to an SProc, instead of a function, is a better way.We have just-in-time caching SProcs that work this way:Can I find data in the cache? Yes = return itNo = select all the relevant data, return that to the application instead, along with a "Store this in the cache when you have processed it" return value.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-05 : 14:33:28
|
so you mean: function is used to validate the data being sent over by the sproc... inside the function is an sproc that inserts records?But the keyword is (see red text)So I'll do it this way, if you're still interested...stored proc that sends the parameters to the function that validates the record...function returns true or false or whatever computations you want it returned... sproc evaluates the returned value and decides if it inserts or nototherwise (i probably am missing something complex, my mind thinks in simpler ways) you may need to make the insert statement as plain tsql inside your neat functionquote:
Originally posted by markusf
The thing is that this will be used as an interface between two components. so what I want to do is the following.Send in enough data for the function to find the corresponding record in a fuzzy way. If the record does not exist then the storedProc will insert the record into the table. The return will be a string: "[record id]|match-category". The logic used to find the correct record will be fairly complex, and since it might involves insertion, a function can't be used--Markus Foss, Norway
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Calling a StoredProc within a Function