Invalid Insert Statement in the Function

[manishkaushik]

Dear Friends.

I m trying to use the insert statement with in the function !
and i m getting this errror !

Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16
Invalid use of 'INSERT' within a function.

Please help me how to rectify it and how i can use the Insert statement with in the function !

Here is the code for the function.


create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as
begin
Declare @valCost integer
Declare @TotService integer
Declare @TotParts integer
Declare @TotLabour integer
Declare @TotTravel integer
Declare @TotSubContract integer
select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no
insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1)
return (@valCost)
end

[Rawhide]

You are not allowed to modify table data from within a user-defined function. This means no updates, inserts, or deletes of data from tables.

These aren't like the VB Function subroutines. These are like core functions like IsNull() and RTrim().

[manishkaushik]

Thank u Rawhide
Thank u for your response.

I just gone through with some more tutorial and i come to know that i can use the Stored Procedure for that.