正文
SqlServer简单的操作XML以及SQl的 try catch等统一格式
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
1：SqlServer简单的操作XML：
ALTER PROCEDURE [dbo].[SP_CRM_FranchiseeRecharge_Money]
@Create_By VARCHAR(),
@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></age>
</person>
</root>'
) DECLARE @errorCode INT;
DECLARE @errorMsg VARCHAR();
BEGIN TRY
SET @errorCode = ;
SET @errorMsg = '';
DECLARE @xml XML;
SELECT @xml = xmlinfo
FROM dbo.qqtest
WHERE id =; 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 = ;
END TRY
BEGIN CATCH
SET @errorCode = ;
SET @errorMsg = ERROR_MESSAGE();
RAISERROR(N'error：XML文件格式错误：', , ,@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); 写入临时表
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;
