namelesstavern.net

SQL Server 2005
I'm trying to create a function that will return a value from a field in a table then delete that row. Below is the code I'm testing with. When i try to create the fuction it gives the error:

Msg 443, Level 16, State 15, Procedure RecieveMessage, Line 21
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.

Any help would be greatly appreciated.

Thanks,
Nathan

USE MobileData
GO
CREATE FUNCTION RecieveMessage(@Company NVARCHAR(100), @Technician NVARCHAR(100))
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @UniqueID uniqueidentifier
SELECT @UniqueID =
(
SELECT TOP(1) UniqueID
FROM mobiledata.dbo.Connection
WHERE company = @company AND technician = @technician
)

DECLARE @MessageString NVARCHAR(1000)
SELECT @MessageString =
(
SELECT MessageString
FROM mobiledata.dbo.Connection
WHERE UniqueID = @UniqueID
)

DELETE FROM MobileData.dbo.Connection
WHERE @UniqueID = UniqueID
RETURN
@MessageString
END

MSSQL doesn't allow editing of tables from within user defined functions.

Call the fuction from a stored procedure that returns the information then deletes the record?