调用方法GetList(bool resle) ,传-1查询所有，0或1查询相关数据！

Select
   [wifiId],
   [ssid],
   [name],
   [orderby],
   [release]
  FROM
   [wifi_data]
  where
   [release] = @onlyRelease OR @onlyRelease = -1
  ORDER  BY
   [orderby] asc

 

----------------------------------------------------------

ALTER PROCEDURE dbo.Zhwcp_Wifi_UpdateOrder
 @wifiId             INT,
 @isUp    BIT
AS
/*
功能：修改类型信息排序。
@wifiId   类型编号
@isUp     是否上移
*/
 SET NOCOUNT ON
 
 --用于存储排序号的临时表
 DECLARE @tempTable TABLE ([id]  INT IDENTITY , [wifiId] INT , [orderBy] INT)
 INSERT
  @tempTable
 (
  [wifiId]
 )
 SELECT
  [wifiId]
 FROM
  [wifi_data]
 ORDER BY
  [orderBy] ASC ,
  [wifiId] ASC
 
 UPDATE
  @tempTable
 SET
  [orderBy] = [id]
  
 --取得当前的排序号
 DECLARE @orderBy INT
 SELECT
  @orderBy = [orderBy]
 FROM
  @tempTable
 WHERE
  [wifiId] = @wifiId
 
 
 IF @orderBy IS NULL
 BEGIN
  SET NOCOUNT OFF
  RETURN
 END 
 
 --更新临时表里的排序号
 IF @isUp = 1 AND @orderBy > 1
 BEGIN
  --上移一位
  IF EXISTS (SELECT 0 FROM @tempTable WHERE [orderBy] < @orderBy)
  BEGIN
   UPDATE
    @tempTable
   SET
    [orderBy] = [orderBy] - 1
   WHERE
    [id]  = @orderBy
   
   UPDATE
    @tempTable
   SET
    [orderBy] = [orderBy] + 1
   WHERE
    [id]  = @orderBy - 1
  END
 END
 ELSE IF @isUp = 0
 BEGIN
  --下移一位
  IF EXISTS (SELECT 0 FROM @tempTable WHERE [orderBy] > @orderBy)
  BEGIN
   UPDATE
    @tempTable
   SET
    [orderBy] = [orderBy] + 1
   WHERE
    [id]  = @orderBy
   
   UPDATE
    @tempTable
   SET
    [orderBy] = [orderBy] - 1
   WHERE
    [id]  = @orderBy + 1
  END
 END
 
 --更新排序号， 只更新改变了的
 UPDATE
  [wifi_data]
 SET
  [orderBy] = B.[orderBy]
 FROM
  [wifi_data] A ,
  @tempTable B
 WHERE
  A.[wifiId] = B.[wifiId]
 AND
  A.[orderBy]  <> B.[orderBy]
 
 SET NOCOUNT OFF

 

 

 

 

--------------------------------

ALTER PROCEDURE dbo.Zhwcp_Wifi_Update
   (
 @wifiId                                                  INT,
 @ssid                                                NVARCHAR(50),
 @name                                                NVARCHAR(100),
 @orderby                                             INT,
 @release                                             BIT
   )
AS
/*
功能:
*/
 SET NOCOUNT ON
 
 DECLARE @orderbycount INT
  
   set
   select @orderbycount = Max([orderby])
  
   From
   [wifi_data]
  
    IF  ( @orderbycount IS null)
    BEGIN
    
    set
    
    @orderbycount = 0
    
    END

 
 IF NOT EXISTS (SELECT 0 FROM [wifi_data] WHERE [wifiId] = @wifiId)
 BEGIN
  INSERT
   [wifi_data]
  (
   [ssid],
   [name],
   [orderby],
   [release]
  )
  VALUES
  (
   @ssid,
   @name,
   @orderbycount + 1,
   @release
  )
 END
 ELSE
 BEGIN
  UPDATE
   [wifi_data]
  SET
   [ssid]                                                = @ssid,
   [name]                                                = @name,
   [orderby]                                             = @orderbycount + 1,
   [release]                                             = @release
  WHERE
   [wifiId]            = @wifiId
   
  
 END
 
 
  
  
  
  
  
 
 SET NOCOUNT OFF