Insert Statement in Function - SQL Server Forums

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)

Insert Statement in Function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-27 : 08:00:49

Manish writes "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 !

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
select @TotLabour= isnull(sum(normalcost)+sum(otcost),0) From SB_labour_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotTravel= isnull(sum(km*costprice),0) From SB_Travel_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotSubContract= isnull(sum(quantity*costprice),0) From SB_SubContract_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
set @valCost=@TotService + @TotParts + @TotLabour + @TotTravel + @TotSubContract
insert into dbo.SB_InvoiceCostingService values(@varWork_no,@varSubWork_no,@TotService,@TotParts,@TotLabour,@TotTravel,@TotSubContract,@valCost)
return (@valCost)
end"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 08:03:36

User-defined functions cannot make permanent changes to database objects, e.g. inserting data into a table. Change this code to a stored procedure and it will work fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources