title: SQL Server stored procedure Update Record Using Select Query
body: <p>I am learning SQL Server stored procedures by way of writing a C# application for holiday bookings.  </p>
  
  <p>One of the stored procedures updates records within a table based if that record exists in a select query which has relationships with other tables and the table I am trying to update. The stored procedure executes fine and returns a status of 0 which implies nothing went wrong but the records are not being updated with the new status field value.  </p>
  
  <p>If I take the select query out of the stored procedure and run this by it's self it does return the records I expected to be updated with the new status column value. I am a bit lost, is it something to do with locking in that I cannot update a table where I am also selecting from the same table?</p>
  
  <p>The stored procedure code is as follows:</p>
  
  <pre><code>SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[spDeletetblBalancesUsingTypeURNAndRoleURN]
      @ABSENCE_TYPE_URN uniqueidentifier,
      @ROLE_URN uniqueidentifier,
      @LAST_USER_SAVED uniqueidentifier
  AS
  BEGIN
      BEGIN TRY
          BEGIN TRANSACTION
              UPDATE [dbo].[tblBalances]
              SET 
                  [STATUS] = 0
              WHERE
                  [tblBalances].[URN] IN
                  (
                      SELECT
                          [tblBalances].[URN]
                      FROM
                          [tblRoles] LEFT JOIN [tblGroupsAssignedToAbsenceTypes] ON [tblRoles].[URN] = [tblGroupsAssignedToAbsenceTypes].[ROLE_URN] 
                          LEFT JOIN [tblUsersAssignedToRoles] ON [tblGroupsAssignedToAbsenceTypes].[ROLE_URN] = [tblUsersAssignedToRoles].[ROLE_URN] 
                          LEFT JOIN [tblBalances] ON [tblUsersAssignedToRoles].[USER_URN] = [tblBalances].[USER_URN]
                  WHERE
                      [tblRoles].[URN] = @ROLE_URN 
                      AND
                      [tblGroupsAssignedToAbsenceTypes].[ABSENCE_TYPE_URN] = @ABSENCE_TYPE_URN 
                      AND
                      [tblUsersAssignedToRoles].[STATUS] = 1
                      AND
                      [tblGroupsAssignedToAbsenceTypes].[STATUS] = 1
                      AND
                      [tblBalances].[ABSENCE_TYPE_URN] = @ABSENCE_TYPE_URN 
                      AND
                      [tblBalances].[STATUS] = 1
                      AND
                      ISNULL([tblBalances].[EXPIRY_DATE], '1900-01-01 00:00:00.000') &lt;&gt; '1900-01-01 00:00:00.000'
              )
      COMMIT TRANSACTION
      BEGIN TRANSACTION
          INSERT INTO [dbo].[tblAudit]
          ([TRANSACTION_USER], [TABLE_NAME], [CURRENT_VALUES], [NEW_VALUES])
          VALUES
          (@LAST_USER_SAVED, 'tblBalances', 'ABSENCE_TYPE_URN : ' + CONVERT(nvarchar(36), @ABSENCE_TYPE_URN) + CHAR(13) + CHAR(10)
              + 'ROLE_URN : ' + CONVERT(nvarchar(36), @ROLE_URN)
              , 'Delete Record')
          COMMIT TRANSACTION
  
          SELECT 0
      END TRY
      BEGIN CATCH
          ROLLBACK TRANSACTION
          SELECT 1
      END CATCH
  END
  </code></pre>