update Command set state = 'A' where get_session = @saved_session. 

update Command set state = 'E' where Id = @id

	<info>
		<command>
			<id> 1 </id>
			<state>P</state>
		</command>
		...
		<command>
			<id> 7 </id>
			<state>E</state>
		</command>
		...
		<command>
			<id> 2 </id>
			<state>A</state>
		</command>
	</info>

ALTER PROCEDURE [dbo].[set_command_state] 
	@commands xml
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	with cte_ids as
	(
		select 
			t.c.value('(state)[1]', 'char(1)') state, 
			t.c.value('(id)[1]', 'int') id
		from 
			@commands.nodes('/info/command') t(c)
	)

	update dbo.Command
	set State = cte.state
	from dbo.Command cmd 
		inner join cte_ids cte on cmd.Id = cte.id
END

CREATE TABLE [dbo].[Command](
	[Id] [int] IDENTITY( 1 , 1 ) NOT NULL,
	[Request] [xml] NULL,
	[Response] [xml] NULL,
	[State] [char]( 1 ) NOT NULL,
	[last_update] [datetime] NOT NULL,
	[sys_user] [varchar]( 255 ) NOT NULL,
	[get_session] [varchar]( 255 ) NULL,
	[Target] [varchar]( 1000 ) NULL
)

ALTER PROCEDURE [dbo].[get_command_new] 
	@ServiceType varchar( 50 ) = 'B'		
AS
BEGIN
	declare @tmp_command_storage table 
		([Id] int not null, 
		 [Request] xml not null,
		 [RequestTime] datetime not null,
		 [Target] varchar( 255 ) not null)

	begin tran
		begin try
			insert into @tmp_command_storage
			select [Id]
				  ,[Request]
				  ,[RequestTime]
				  ,[Target]
			  from [dbo].[Command] c (UPDLOCK) 
			 where [Service] = Upper(@ServiceType)
			
			UPDATE [dbo].[Command]
			   SET [State] = 'P'
			 WHERE Command.Id in (select Id from @tmp_command_storage)
		end try
		begin catch
                        -- некая обработка ошибок
		end catch

	if @@TRANCOUNT >  0 
	commit tran

	select * from @tmp_command_storage
	return @@ROWCOUNT
END