Insert record and return Id in a SQL UDF?

Question

I need a user defined function to insert a new record and return the Id, is this possible and how do I do it? I have tried creating a function with the insert statement but I am getting the following error: ``` Invalid use of the side-affecting operator 'INSERT' within a function. ``` I am using SQL server 2008. UPDATE SQL: ``` CREATE FUNCTION dbo.GetNewBookingReference() RETURNS int AS BEGIN INSERT INTO BookingReference (CreatedTime, CreatedByUserId) VALUES (GETDATE() ,10) RETURN @@IDENTITY END ``` Thanks

Accepted Answer

In SQL Server, user-defined functions cannot change database state. You will have to use a stored procedure with an `OUTPUT` parameter.

Insert record and return Id in a SQL UDF?

Solution

Problem