| Author |
 Topic  |
|
|
Jayesh
Starting Member
India
7 Posts |
 Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3437 Posts |
Posted - 01/21/2010 : 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 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 06:37:07
|
| use OPENQUERY() ? |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3437 Posts |
Posted - 01/21/2010 : 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 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 06:43:08
|
| (Not a good idea though, as T.C. said) |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3437 Posts |
Posted - 01/21/2010 : 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 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 07:24:11
|
| I always pass RAND() or GETDATE() in as a parameter when I need them ... |
|
|
|
Jayesh
Starting Member
India
7 Posts |
Posted - 01/22/2010 : 07:20:43
|
Thanks a lot Transact Charlie and Kristen...
Jayesh |
Edited by - Jayesh on 01/22/2010 07:21:56 |
|
|
| |
Topic |
|
About Stored Procedures
