Calling extended stored procs from a function

I know you cannot call functions from a stored proc.. only extended stored
procs otherwise you get this message
"Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
Only functions and extended stored procedures can be executed from within a
function."
So, to get around it, I am using sp_Executesql which IS an extended stored
proc but I am still getting the error. PLease explain?
Thanks!!!!!
ALTER function nextval2
( @.sequence varchar(100)) returns int
AS
BEGIN
declare @.sequence_id int
set @.sequence_id = -1
exec sp_executesql N'update sequences set sequence_id = sequence_id + 1'
select @.sequence_id = max(sequence_id)
from sequences
RETURN @.sequence_id
ENDThere are 2 ways that I know of to maintain variable information between
calls to a function,
1) Use OPENQUERY to pass through an UPDATE or INSERT to a loopback linked
server.
2) Use sp_OA calls to access parameters on a previously declared object (e.g
VBScript.RegExp)
I suspect these are just about as dangerous as each other :)
Mr Tea
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:C92D80A6-1367-4BF5-9BE2-65BD8C54EC12@.microsoft.com...
>I know you cannot call functions from a stored proc.. only extended stored
> procs otherwise you get this message
> "Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
> Only functions and extended stored procedures can be executed from within
> a
> function."
> So, to get around it, I am using sp_Executesql which IS an extended stored
> proc but I am still getting the error. PLease explain?
> Thanks!!!!!
> ALTER function nextval2
> ( @.sequence varchar(100)) returns int
> AS
> BEGIN
> declare @.sequence_id int
> set @.sequence_id = -1
> exec sp_executesql N'update sequences set sequence_id = sequence_id + 1'
> select @.sequence_id = max(sequence_id)
> from sequences
> RETURN @.sequence_id
> END
>|||Thanks LEe.
"Lee Tudor" wrote:

> There are 2 ways that I know of to maintain variable information between
> calls to a function,
> 1) Use OPENQUERY to pass through an UPDATE or INSERT to a loopback linked
> server.
> 2) Use sp_OA calls to access parameters on a previously declared object (e
.g
> VBScript.RegExp)
> I suspect these are just about as dangerous as each other :)
> Mr Tea
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:C92D80A6-1367-4BF5-9BE2-65BD8C54EC12@.microsoft.com...
>
>