Invalid Insert Statement in the Function

Question

Post reply

Invalid Insert Statement in the Function

Manish_

Ten Centuries

Points: 1070
More actions
April 25, 2005 at 11:30 pm

#164179

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

- Manish

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Manish - here's something from BOL that explains this:

***************************************

You may have included a statement in the BEGIN-END block that has side effects, which is not allowed in user-defined functions. Function side effects are any permanent changes to the state of a resource that has a scope outside the function. Changes can be made only to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user.

**************************************

Several things:

1) Why can't you use a stored procedure ?!

2) What is your @valCost returning ?

3) Assuming you have pasted the full code here, what are your other variables being used for - @totlabour, @tottravel etc..????

**ASCII stupid question, get a stupid ANSI !!!**

Manish_ Ten Centuries Points: 1070 More actions · Answer 2

Hi Shushila

Thank u so much for your reply ,

1) I m not using the storedprocedure,

because i can;t call them within the sql query in VB.net forms.

2) and i m sorry for not writing the full code,

actually the @valCost is returning the sum of @TotService and @TotParts,

@valCost = @TotService + @TotParts

3) and as i had told u that i had not pasted the whole code here. so these variable i need later on and while pasting

on forum i forget to delete them , and i m sorry for that,

But i need this thing from this code

1. I want to return the value .

2. I need to call t he insert statement.

3. i want to call this function with in the simple sql query in the vb.net forms.

but if i m unable to do the these all , the last option which i have is going for Stored Procedure.

or if u have any different idea then please guide me.

- Manish

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Manish - a quick "google" got me the following info that you may find useful - I don't use vb.net but I use vb 6.0 and call stored procedures all the time...here's the info on how to call procedures from a .net application - the example i saw returned an array of values and I modified it to read just one....I'm sure you can get syntax help once you have most of the code in place...HTH.

******************************

Using the Command Object to Execute a Stored Procedure

In order to access the stored procedure from a .NET client application, you use the System.Data.SqlClient namespace. This namespace contains the objects used to interact with SQL Server 7.0 and above. A SqlConnection object is used to establish a connection into the database. Once the connection is established a SqlCommand object is used to execute SQL statements or Stored Procedures. The SqlCommand object contains four different methods for executing statements against the database. The ExecuteReader method is used to execute commands that return records. The ExecuteNonQuery is used to execute commands that do not return records such as update and insert statements. The ExecuteScalar method is used to execute a command that returns a single value rather than a result set. The ExecuteXmlReader is used to execute a command that returns the results in an XML formatted string.

The CommandType property of the SqlCommand object is used to indicate what type of command is being executed. The CommandType property is set to one of three possible CommandType enumeration values. The default Text value is used when a SQL string is passed in for execution. The StoredProcedure value is used when the name of a stored procedure is passed in to execute. The TableDirect value is used when a table name is being passed in. This setting will pass back all the records in the table. The CommandText property of the SqlCommand object is used in conjunction with the CommandType property. The CommandText property will contain a SQL string, stored procedure name, or table name depending on the setting of the CommandType property.

How to call a procedure from vb.net:

Dim MyConnection As New SqlConnection("server=localhost;integrated security=true;" + "database=dbname")

Note: This assumes you have a local instance of SQL Server and are logged on with a trusted connection.

Next, create a SqlCommand object and set the appropriate properties needed to execute the up_Procedure stored procedure you have created.

Dim cmdValCost As New SqlCommand("up_Procedure", MyConnection)

cmdValCost.CommandType = CommandType.StoredProcedure

The next step is to use the SqlCommand object to create an instance of the SqlDataReader class. The SqlDataReader class is used to read a forward only stream of records returned from the database. The SqlDataReader object is not instantiated directly through a constructor (hence the lack of the New key word) but rather through the ExecuteReader method of the SqlCommand object. Before calling the ExecuteReader method the connection to the database is established using the Open method of the SqlConnection object.

Dim drValCost As SqlDataReader

MyConnection.Open()

drValCost = cmdValCost.ExecuteReader()

Now that the stored procedure has been executed, the Read method of the SqlDataReader object is used to read the records and pass them into an ArrayList that will be returned to the caller. The Read method returns a value of False when it reaches the end of the records. Before exiting the method, the close methods of the SqlDataReader and the SqlConnection objects are called.

Dim intValue As integer

While drValCost.Read()

intvalue = drValCost.GetValue(0))

End While

drValCost.Close()

MyConnection.Close()

*************************************

**ASCII stupid question, get a stupid ANSI !!!**