SqlServer简单的操作XML以及SQl的 try catch等统一格式

50), @xmlStr1 NVARCHAR(MAX) -写在这里面的为 传入的参数 AS BEGIN as begin后面的为自定义的变量 SET NOCOUNT ON; DECLARE @xml XML = @xmlStr1; sqlserver操作XML T-SQL提供了以下几个针对XML类型的查询函数： query(XQuery)：利用Xquery查询符合条件的元素集合。 value(XQuery,Type)：利用Xquery查询具体元素的值，Type定义值的类型。 exist(XQuery)：利用Xquery查询是否存在符合条件的元素。 nodes(XQuery)：利用Xquery获得一个结果集。 INSERT INTO qqtest(xmlinfo) xmlinfo 为字符串类型 VALUES( N‘<root> <person> <id>F001</id> <name>F001</name> <age>18</age> </person> </root>‘ ) DECLARE @errorCode INT; DECLARE @errorMsg VARCHAR(2000); BEGIN TRY SET @errorCode = 0; SET @errorMsg = ‘‘; DECLARE @xml XML; SELECT @xml = xmlinfo FROM dbo.qqtest WHERE id =6; SELECT S.value(‘id[1]‘, ‘varchar(30)‘) id, S.value(‘name[1]‘, ‘varchar(30)‘) _Name, S.value(‘age[1]‘, ‘varchar(30)‘) age FROM @xml.nodes(‘/root/person‘) AS A(S); //B(S) 都是没有问题的 @xml.nodes(‘/root/person‘) AS A(S) 当做一张表来操作了 SET @errorCode = 200; END TRY BEGIN CATCH SET @errorCode = 500; SET @errorMsg = ERROR_MESSAGE(); RAISERROR(N‘error：XML文件格式错误：‘, 16, 1,@errorMsg); PRINT(@errorMsg) END CATCH 获取XML数据 ，把XML数据当做一个表格 @xml.nodes(‘/Root/data‘) AS T(S); select S.value(‘ID[1]‘,‘int‘)as ID, S.value(‘Name[1]‘,‘nvarchar(50)‘)as Name FROM @xml.nodes(‘/Root/data‘) AS T(S); 2 写入临时表 INSERT INTO #tbCRM_FranchiseeRecharge ( [Recharge_ID], [Franchisee_ID], [Recharge_Credit], [Recharge_Amount] ) SELECT DISTINCT NEWID(), S.value(‘Franchisee_ID[1]‘, ‘uniqueidentifier‘) AS Franchisee_ID, S.value(‘Recharge_Credit[1]‘, ‘int‘) AS Recharge_Credit, S.value(‘Recharge_Amount[1]‘, ‘money‘) AS Recharge_Amount FROM @xml.nodes(‘/Root/data‘) AS T(S);

 

2：SQl语句简单的 try catch 的统一格式：如下简单的Sql

  string sql = @"BEGIN
                                SET @insertStateCode = 0;
                                IF NOT EXISTS
                                (
                                    SELECT TOP 1
                                           *
                                    FROM tbCRM_Franchisee_Credit_History
                                    WHERE Seq =@rechangeID
                                )          
                                BEGIN TRY
                                    BEGIN TRAN mytran; 
                                    INSERT INTO tbCRM_Franchisee_Credit_History
                                    (
                                        [Seq],
                                        [Franchisee_ID],
                                        [Tran_Code],
                                        [Tran_By],
                                        [Tran_Date],
                                        [Credit],
                                        [Amount],
                                        [Remark]
                                    )
                                    SELECT RM.Recharge_ID,
                                           RM.Franchisee_ID,
                                           5,
                                           RM.Create_By,
                                           GETDATE(),
                                           RM.Recharge_Credit,
                                           RM.Recharge_Amount,
                                           RM.Remark
                                    FROM tbCRM_Franchisee_Recharge_Master RM
                                    WHERE RM.Recharge_ID = @rechangeID;                            
                                    UPDATE tbCRM_Franchisee_Recharge_Master
                                    SET Approve_By = 1
                                    WHERE Recharge_ID =@rechangeID;
                                    SELECT @insertStateCode =200;
                                    COMMIT TRAN mytran;
                                END TRY
                                BEGIN CATCH
                                    SELECT @insertStateCode = 500;
                                    ROLLBACK TRAN mytran;
                                END CATCH;
                            END;";

其实就是简单如下：

BEGIN TRY
    BEGIN TRAN mytran; 

END TRY
BEGIN CATCH
    SELECT @insertStateCode = 500;
      ROLLBACK TRAN mytran;
END CATCH;

 

SqlServer简单的操作XML以及SQl的 try catch等统一格式

标签：tst   orm   存在   写入   select   mon   history   his   lse