SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 About Stored Procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jayesh
Starting Member

India
7 Posts

Posted - 01/21/2010 :  06:19:29  Show Profile  Reply with Quote
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
3451 Posts

Posted - 01/21/2010 :  06:34:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  06:37:07  Show Profile  Reply with Quote
use OPENQUERY() ?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  06:41:55  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  06:43:08  Show Profile  Reply with Quote
(Not a good idea though, as T.C. said)
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  06:48:39  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  07:24:11  Show Profile  Reply with Quote
I always pass RAND() or GETDATE() in as a parameter when I need them ...
Go to Top of Page

Jayesh
Starting Member

India
7 Posts

Posted - 01/22/2010 :  07:20:43  Show Profile  Reply with Quote

Thanks a lot Transact Charlie and Kristen...

Jayesh

Edited by - Jayesh on 01/22/2010 07:21:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000