Only functions and extended stored procedures can be executed from within a function

Hi everybody,

When i try to excute a function i got the following error message:
" Only functions and extended stored procedures can be executed from within a function."

I wrote the following stored procedure wich update the sequence id :
---------------------
CREATE PROCEDURE dbo.pr_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100),
@.p_id_sequence numeric OUTPUT ) AS

-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @.v_id_sequence numeric

SET @.v_id_sequence = -1

UPDATE GST_SEQUENCE
SET @.v_id_sequence = ID_SEQUENCE = ID_SEQUENCE + 1
WHERE NOM_SEQUENCE = @.p_nom_sequence
AND NOM_TABLE = @.p_nom_table

SET @.p_id_sequence = @.v_id_sequence
RETURN @.p_id_sequence

Then, i wrote the following function which return the last value of sequence id:
--------------------
CREATE FUNCTION dbo.fu_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100)
) returns int AS

BEGIN
DECLARE @.v_id_sequence numeric

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence

RETURN @.v_id_sequence

END

And, when i try to call the function, i got the error message:
-------------------
select dbo.fu_get_id_sequence ('SEQ', 'TABLE')

By the way i'm using SQL Server 2005. Also, I tried to create the function by incliding the UPDATE statement but it didn't work.

Can anyone help me ?
Thank you

PaulThe error messages clearly point to the source of error: you cannot call stored procedures, as well as perform updates, deletes and inserts in T-SQL functions. That's why you cannot execute your function. You'll have to create stored procedure instead of function to be able to call another stored procedure. Also, all output parameters should be explicitly marked as output when you call a stored procedure:

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence output