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 |
|
Jayesh
Starting Member
7 Posts |
 Posted - 2010-01-21 : 06:19:29
|
Hi All, I have build some SP's and some functions, my problem is that, I can call functions in a SP but I am not able to call a SP in a function. Can anyone help me with any solution by which I can call a SP in a Scalar valued functions Jayesh |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 06:34:17
|
| you can't (at least on sql server 2000 and 2005 - not sure about 2008)Functions are not allowed to change permanent data in any way and one of the ways used to enforce this is that they cannot call external objects that could change data -- also dynamic sql is not allowed.What do you want your function to do?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:37:07
|
| use OPENQUERY() ? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 06:41:55
|
| hmmmm. Yes, that *would* work. Way to prove me wrong Kristen!But - Functions have to be deterministic (that is given the same data in they produce the same data out which is why you can't call things like GETDATE() etc. from inside a function directly. OPENQUERY does give you a way round that but it's really messy.If you post the code of the function and what you need it to do there will probably be a better way to rewrite it rather than using OPENQUERY.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 06:43:08
|
| (Not a good idea though, as T.C. said) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-21 : 06:48:39
|
| If you are trying to get a nondeterministic value in a function (like RAND() or GETDATE()) then you can set up a view with a column value of that function. You can reference that inside your function. A common use would be a random string function which spits out a randomised VARCHAR. because you can't call RAND() inside the function you would query the view instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 07:24:11
|
| I always pass RAND() or GETDATE() in as a parameter when I need them ... |
|
|
|
Jayesh
Starting Member
7 Posts |
Posted - 2010-01-22 : 07:20:43
|
| Thanks a lot Transact Charlie and Kristen...Jayesh |
|
|
|
|
|
|
|
|
About Stored Procedures