Update Within a SQL Server Function

Question

I have a SQL Server function that gets a nextID from a table. That is straight-forward enough, but I also want to update the table for the ID used. I am using the technique to update into a table, but the update just isn't happening. The code is shown below, anyone know why?

CREATE FUNCTION [dbo].[GetNextKeyID]
(
 @user nvarchar(10),
 @keytype nvarchar(20) 
)
RETURNS nvarchar(15)
AS
BEGIN
    DECLARE @NextID nvarchar(15);

    -- get the next id for this keytype
    SELECT @NextID = @user + '_' + @keytype + '_' + CONVERT(VARCHAR(3), 
                                                            (SELECT CASE WHEN @keytype = 'sess' THEN SessionNext
                                                                         WHEN @keytype = 'event' THEN EventNext
                                                                         WHEN @keytype = 'evtc' THEN EventConsultantNext
                                                                         WHEN @keytype = 'act' THEN ActivityNext
                                                                     END)
                                                            )
    FROM dbo.[BARKeys]
    WHERE [UserID] = @user 

    -- increment the next id for this keytype
    DECLARE @BARKeys TABLE
    (
        UserID nvarchar(10),
        SessionNext int,
        EventNext int,
        EventConsultantNext int,
        ActivityNext int
    )

    UPDATE @BARKeys
    SET SessionNext         = CASE WHEN @keytype ='sess' OR @keytype = 'All' 
                                   THEN SessionNext + 1 
                                   ELSE SessionNext END
       ,EventNext           = CASE WHEN @keytype ='event' OR @keytype = 'All' 
                                   THEN EventNext + 1 
                                   ELSE EventNext END
       ,EventConsultantNext = CASE WHEN @keytype ='evtc' OR @keytype = 'All' 
                                   THEN EventConsultantNext + 1 
                                   ELSE EventConsultantNext END
       ,ActivityNext        = CASE WHEN @keytype ='act' OR @keytype = 'All' 
                                   THEN ActivityNext + 1 
                                   ELSE ActivityNext END
    WHERE [UserID] = @user

    -- return the next id
    RETURN  @NextID ;
END

Answer 1

No you cannnot do this. Functions are used for readonly purpose.

User-defined functions cannot be used to perform actions that modify the database state.

Check this out

Work around is to create a STORED PROCEDURE