cs_Link_UpdateSortOrder ALTERprocedure[dbo].cs_Link_UpdateSortOrder ( @LinkIDint, @SettingsIDint, @MoveUpbit ) AS SETTransactionIsolationLevelReadUNCOMMITTED /**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁, 也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更 改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有 语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/ BEGIN set nocount on /**//*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。 当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。*/ DECLARE@currentSortValueint--当前类型的值 DECLARE@replaceSortValueint--替换类型的值 DECLARE@replaceLinkIDint--替换连接ID DECLARE@LinkCategoryIDint--连接种类ID -- Get the current sort order --获取当前排序类型 SELECT@currentSortValue= SortOrder, @LinkCategoryID= LinkCategoryID FROM cs_Links WHERE LinkID =@LinkIDand SettingsID =@SettingsID -- Move the item up or down? --- IF (@MoveUp=1) BEGIN SELECT@replaceSortValue=coalesce(f.SortOrder, -1), @replaceLinkID=coalesce(f.LinkID, -1) FROM cs_Links f innerjoin ( selecttop1* from cs_Links WHERE LinkCategoryID =@LinkCategoryIDand SortOrder <@currentSortValueand SettingsID =@SettingsIDorderby SortOrder DESC ) as pf on pf.LinkID = f.LinkID and f.SettingsID =@SettingsID /**//*先查询替换种类的值和替换连接的ID,如果都存在就更新*/ if( @replaceSortValue!=-1And@replaceSortValueisNotNull ) begin UPDATE cs_Links SET SortOrder =@currentSortValueWHERE LinkID =@replaceLinkIDand SettingsID =@SettingsID UPDATE cs_Links SET SortOrder =@replaceSortValueWHERE LinkID =@LinkIDand SettingsID =@SettingsID END END ELSE BEGIN SELECT@replaceSortValue=coalesce(f.SortOrder, -1), @replaceLinkID=coalesce(f.LinkID, -1) FROM cs_Links f innerjoin ( selecttop1* FROM cs_Links WHERE LinkCategoryID =@LinkCategoryIDand SortOrder >@currentSortValueand SettingsID =@SettingsIDorderby SortOrder ASC ) as pf on pf.LinkID = f.LinkID and f.SettingsID =@SettingsID if( @replaceSortValue!=-1And@replaceSortValueisNotNull ) begin UPDATE cs_Links SET SortOrder =@currentSortValueWHERE LinkID =@replaceLinkIDand SettingsID =@SettingsID UPDATE cs_Links SET SortOrder =@replaceSortValueWHERE LinkID =@LinkIDand SettingsID =@SettingsID end END /**//*COALESCE 返回其参数中第一个非空表达式*/ END
cs_Link_CreateUpdateDelete ALTERPROCEDURE dbo.cs_Link_CreateUpdateDelete @DeleteLinkbit=0, @LinkCategoryIDint=0, @Titlenvarchar(100)='', @Urlnvarchar(255)='', @Relnvarchar(100)='', @Descriptionnvarchar(2000)='', @IsEnabledbit=1, @SortOrderint=0, @SettingsIDint, @LinkIDint=0 out AS SETTransactionIsolationLevelReadUNCOMMITTED /**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁, 也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更 改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有 语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/ -- Are we deleting? -- 我们是否删除 if@DeleteLink=1-- 如果删除 begin DELETEFROM cs_Links WHERE LinkID =@LinkID end /**//*删除*/ -- Are we updating? -- 我们是否更新 if@LinkID>0--如果大于0,则表示存在 begin UPDATE cs_Links SET Title =@Title, Url =@Url, Rel =@Rel, [Description]=@Description, IsEnabled =@IsEnabled, SortOrder =@SortOrder WHERE LinkID =@LinkIDand LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID end /**//*更新*/ else begin If( @SortOrder=0 ) -- 如果排序类型为0 Begin Select@SortOrder=max(SortOrder) +1from cs_Links where LinkCategoryID =@LinkCategoryIDand SettingsID =@SettingsID if(@SortOrderisnull) Select@SortOrder=0 End /**//*插入数据*/ INSERTINTO cs_Links (LinkCategoryID, Title, Url, Rel, [Description], IsEnabled, SortOrder, SettingsID) VALUES (@LinkCategoryID, @Title, @Url, @Rel, @Description, @IsEnabled, @SortOrder, @SettingsID) set@LinkID=@@IDENTITY end