调用方法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
转载于:https://blog.51cto.com/meili5818/622572