Delete, Insert, Update in Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete, Insert, Update in Function

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-13 : 05:11:29

I created function in SQL 2000 to delete one record like:

create function del_PayMast
(@PayId Numeric) Returns Numeric
As
Begin
Delete From PayMaster Where PayId = @PayId
Return (@@Error)
End
Go

but to showing me an error:

Server: Msg 443, Level 16, State 2, Procedure del_PayMast, Line 5
Invalid use of 'DELETE' within a function.

can't we create function for Delete, Insert, Update. I tried Select in function, it works fine, so i gone for other DMLs. but it is throwing error. why?

It is supported in Oracle. Does SQL 2000 have another way to do this? or it not a feature of SQL 2000 and can be done in SQL 2005?

Thanks in advance,

Mahesh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-03-13 : 06:00:36

You can't do any data manipulation in a user defined function...use a stored procedure instead.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-13 : 06:02:28

quote:
Originally posted by Lumbago

You can't do any data manipulation in a user defined function...use a stored procedure instead.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

i wanted to know, if it is possible in SQL 2005 or not?

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources