doing Insert operation from sqlfunction

Posted by Rajendar.sit under Sql Server on 2/16/2011 | Points: 10 | Views : 2443 | Status : [Member] | Replies : 4
hai sir,
i have one doubt, that is
can we do insert operation from the sqlfunction?.

when i am doing like this
"ALTER function [dbo].[getInsTime](@id int,@empid int,@empname varchar(20))
returns datetime
as
begin
insert into Employee values(@id,@empid,@empname)
declare @res datetime
set @res=(select instime from insertedtime where id=@id)
return @res
end"

it showing the error like this
"Msg 443, Level 16, State 15, Procedure getInsTime, Line 5
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function."
please give me the answer sir.

Rajendar@sureit


Responses

Posted by: Madhu.b.rokkam on: 2/16/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Any specific reason for doing this thru functions, Its always better to use the Stored Procedures.

Thanks and Regards
Madhu

Rajendar.sit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Venkatesh on: 2/16/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

better to use the storeproedures instead of functions

Rajendar.sit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 2/16/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Rajendar

1. Functions should/can be used for READ operations. Not for WRITE.
2. Please use Stored procedure instead.

Note: Only functions, READ Operation or some extended stored procedures can be executed from within a function.

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajendar.sit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 2/16/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Stored procedures are the best option to be used for instead of a function.

Thanks
Karthik
www.f5Debug.net

Rajendar.sit, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response