Msg 443, Level 16, State 15, Invalid use of a side-effecting

i m try write a function in sql server there is a error but not under stand. error is

Msg 443, Level 16, State 15, Procedure get_advice_no, Line 21

Invalid use of a side-effecting operator 'UPDATE' within a function.

Msg 443, Level 16, State 15, Procedure get_advice_no, Line 34

Invalid use of a side-effecting operator 'INSERT' within a function.

my function code is

ALTER FUNCTION [dbo].[get_advice_no](@vcounter_type nvarchar(5) )
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @vadvice_no int;
-- Add the T-SQL statements to compute the return value here
select @vadvice_no = (SELECT (isnull(CONSTANT_NO,0) + 1) FROM CA_CONSTANT
WHERE CONSTANT_TYPE = @vcounter_type);
if @vadvice_no > 0
begin
---if exits(SELECT CONSTANT_NO FROM CA_CONSTANT WHERE CONSTANT_TYPE = @vcounter_type)
--begin
update ca_constant
set constant_no = @vadvice_no
where CONSTANT_TYPE = @vcounter_type;
-- end
end
else
begin
set @vadvice_no = 1;
IF (@vcounter_type = 'ADV_2')
begin
set @vadvice_no = 75001;
end
INSERT INTO CA_CONSTANT(CONSTANT_TYPE,CONSTANT_NO)
VALUES(@vcounter_type,@vadvice_no);
end
RETURN(@vadvice_no);
-- Return the result of the function
END

pls tell me what is wrong

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anil Sharma6y ago
thanks for response i am new in sql-server and asp.net C#. I try it like Oracle function, I solve it through procedure
Jignesh Kumar6y ago
Hi Anil,

Funcations are default not support DML operation and its readonly. INSERT/UPDATE/DELETE operations are not allowed inside function you need to write store procedure to perform DML operation.
Even you can not use store procedure inside function. These are few limitation of function.