Error while executing function

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error while executing function

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2006-12-27 : 10:27:16

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

Paul

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 10:37:41

You can't execute SP from within function. Also you can't use DML statements like INSERT/UPDATE/DELETE from function (except only for table variables).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"