Invalid Insert Statement in the SQL Function

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


Thanks & Regards
Manish Kaushik
manishkaushikAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
You can return this value from a stored procedure.


-----------------------------------------------------------------
create PROCEDURE p_GetTotalCost
@varWork_no numeric,
@varSubWork_no as numeric,
@valCost numeric OUTPUT

AS
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)

SET @valCost = <I dont see where you're setting this value, but this is where you'd set it>
-----------------------------------------------------------------



Are you calling it from VB code, T-SQL, DTS, or what? I'll show how to get the return value.


Also you should define your insert like this:

insert into dbo.SB_InvoiceCostingService (<your field name 1>,  <your field name 2>,....) values(@TotService,@TotParts,1,1,1,1,1,1)
0
 
nmcdermaidCommented:
Looking at BOL, you can only INSERT into local table variables.

--------------------------------
INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
--------------------------------


You could easily create that as a stored procedure. Then it would work.


0
 
manishkaushikAuthor Commented:
Thanks  nmcdermaid

I can use the stored procedure, but the problem is that i have to return the value too from this fucntion and as per my knowledge , i m unable to do so in Stored Procedure.


Can u please guide me how can i do as u said "you can only INSERT into local table variables."

Thanks & Regards
Manish Kaushik
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
manishkaushikAuthor Commented:
Once again thanks alot  nmcdermaid

I done as u said ! with the use of the stored procedure.
and i learnt a very nice thing today !
Thank u so much ! and have a nice day !

Thanks & Regards
Manish Kaushik


0
 
manishkaushikAuthor Commented:
Hi nmcdermaid

Ya i m using this stored Procedure in the VB.NET forms.
i want to call this stored procedure and use the value which this procedure will return.

Thanks & Regards
Manish Kaushik
0
 
nmcdermaidCommented:
OK, you need to use a command object, and you need to define the first two parameters as 'input', and the last one as input/output.

I am a VB6 man myself so if I can't help you, you may need to post to the VB6 section.



Since you are using VB, there is one slight change to the stored proc.. you have to add SET NOCOUNT ON to it:


create PROCEDURE p_GetTotalCost
@varWork_no numeric,
@varSubWork_no as numeric,
@valCost numeric OUTPUT

AS
SET NOCOUNT ON

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)

SET @valCost = <I dont see where you're setting this value, but this is where you'd set it>
0
 
manishkaushikAuthor Commented:
Thank u nmcdermaid

I will do the calling of the Stored Procedure in VB.net forms.
Thank u so much for your co-operation.

Thanks & Regards
Manish Kaushik
0
 
nmcdermaidCommented:
I believe I provided the correct answer to his original post.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.