title: Update Within a SQL Server Function
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>