摘要：有时会在存储过程中处理一些XML格式的数据，所以会用到sp_xml_preparedocument，他可以将XML数据进行读取，然后使用 MSXML 分析器 (Msxmlsql.dll) 对其进行分析。我们就可以很容易的在存储过程中得到XML中我们想要的数据。下面的代码就是使用sp_xml_preparedocument读取XML：代码如下: 1 DECLARE @hdoc int 2 DECLARE @doc varchar(1000) 3 SET @doc =' 4 <ROOT> 5 <Customer CustomerID="VINET" C 阅读全文

摘要：返回执行的上一个 Transact-SQL 语句的错误号.Returns 0 if the previous Transact-SQL statement encountered no errors.A.用 @@ERROR 检测一个特定错误以下示例用@@ERROR在UPDATE语句中检测约束检查冲突（错误 #547）。USE AdventureWorks2012;GOUPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE BusinessEntityID = 1;IF @@ERROR = 547 ... 阅读全文

摘要：ALTER TRIGGER [dbo].[Trigger_userStatusChangeLog] ON [dbo].[Jinbi_User] for UPDATEAS BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @NewUserStatus int;declare @OrgUserStatus int;declare @UserID int;declare @InWhiteList int; 阅读全文

摘要：ALTER PROCEDURE [dbo].[Proc_Test_commit1] @result int output, --成功 1; 失败 0 @message nvarchar(20) output ASBEGIN SET NOCOUNT ON begin try begin tran yy insert into Test_Commit (b) values (3) insert into Test_Commit (a,b) values (1,3) set @result=1 commit tran yy ... 阅读全文

摘要：declare @BusinessID int declare @MSMnum bigint DECLARE MSMnum_cursor CURSOR FOR select BusinessID,MSMnum from #temp1 OPEN MSMnum_cursor FETCH NEXT FROM MSMnum_cursor INTO @BusinessID,@MSMnum WHILE (@@FETCH_STATUS <> -1) BEGIN update A set A.BusinessID=@BusinessID ... 阅读全文

摘要：主要是针对那些一张订单有几条记录的信息，先前寸的EMSGold都是总的，现在想把它更新为按其礼品金币值占比分配快递费用，具体脚本如下：select ID,MAX(SEQ_NO) as MA,COUNT(ID) as Tinto #temp001from dbo.Fact_Order_Giftwhere EMSPayGold >0group by ID having COUNT(ID)>=2 --找出有多条记录的信息--#temp002主要是为了后面算最大SEQ_NO的快递费用做铺垫select ID,SUM(round(EMSPayGold*((GiftGold*GiftNum)* 阅读全文