?
Solved

Invalid Insert Statement in the SQL Function

Posted on 2005-04-25
11
Medium Priority
?
5,766 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:manishkaushik
  • 4
  • 4
8 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13864209
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
 

Author Comment

by:manishkaushik
ID: 13864298
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 13864314
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:manishkaushik
ID: 13864375
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
 

Author Comment

by:manishkaushik
ID: 13864438
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13864502
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
 

Author Comment

by:manishkaushik
ID: 13864771
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 14033302
I believe I provided the correct answer to his original post.

Thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question