SQL Procedure

CREATE PROCEDURE [dbo].[DeleteUser] @ID uniqueidentifier AS DECLARE @AccountName NVARCHAR(50) SELECT @AccountName=AccountName FROM Users WHERE ID=@ID IF @@rowcount=0 BEGIN RETURN 0 END ELSE IF EXISTS(SELECT ID FROM MyProject WHERE (ApprovalLevels.exist('//AccountName[.=sql:variable("@AccountName")]') = 1)) BEGIN RETURN -1 END ELSE BEGIN SET XACT_ABORT ON BEGIN TRANSACTION DELETE UserRoles WHERE UserID=@ID DELETE Users WHERE ID = @ID COMMIT TRANSACTION RETURN 0 END

 

 

更新数据库中xml列中的数据，使用知识xquery

update MyProject(表) set ApprovalLevels(xml列).modify

('

replace value of (//AccountName/text())[1]
with string("df-devws/dfzone")

')