How do I use an UPDATE statement in a function? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How do I use an UPDATE statement in a function?

Any suggestions?
I’m getting an error when trying to use an UPDATE statement inside of a function.
error: Invalid use of side-effecting or time-dependent operator in ‘UPDATE’ within a function.

No, you cannot use the EXECUTE (string) statement in a function. You can, however, design a procedure with the same logic.If the table schema is fixed then you can use a VIEW with UNION of SELECt between these tables and also make use of dynamic SQL:
Refer to http://www.sommarskog.se/dynamic_sql.html and http://www.sommarskog.se/dyn-search.html links.

[quote user="kingofSQL"]
Any suggestions?
I’m getting an error when trying to use an UPDATE statement inside of a function.
error: Invalid use of side-effecting or time-dependent operator in ‘UPDATE’ within a function.
[/quote]
Why dont you make it as a procedure instead of function?
Can you please post your code?

I am getting the same error as well.
Here are the code for the same.
I would like to convert one procedure to function and call that function into select statement.

Procedure listed as below:
CREATE PROCEDURE Test_NextEmployee
AS
SET NOCOUNT ON
DECLARE @iReturn int
BEGIN TRANSACTION
SELECT @iReturn = Sequence FROM .dbo.EMPSEQUENCE (TABLOCKX) — set exclusive table lock
UPDATE dbo.EMPSEQUENCE SET Sequence = ( Sequence + 1 )
COMMIT TRANSACTION
SELECT @iReturn
RETURN @iReturn

And after that i have to call that function into below Insert Statement in place of AutoKey column.

Insert Into EmpSequence (KeySegment, Keystn, KeySegmentType, FinlSegment, Status)
select ‘Auto key’, eop.Keystn, 0, a.FinlSegment, 2
from KeyEOP KP
join KeyEOPSegmentDetail a on a.KeyEOP=kP.KeyEOP and a.updop < 2
join Kfinl f on eop.keyeop = f.keyfeop
join FinlSegmentDetail ff on f.keyfinl = ff.keyfinl and ff.updop < 2
and ff.finlsegment = a.finlsegment
join stn I on i.Keystn=EOP.Keystn and Class in ( 8,15)
where EOP.UpdOp<>2
Can anyone please help with that?
Thanks

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |