有关存储过程问题？请大家帮忙。关于显示控制的，新增加一个字段，让他置顶。
公司里自定义的新闻控件，现在新加一个功能。
要改存储过程。
代码如下：
USE JNews;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N '[dbo].[procNews_Add_Show_Control] ') AND type in (N 'P ', N 'PC '))
DROP PROCEDURE [dbo].[procNews_Add_Show_Control]
GO
CREATE PROCEDURE procNews_Add_Show_Control
(
@Sub_Systemint, -- 子系统
@Category_IDint, -- 新闻分类
@News_Codechar(16), -- 新闻编号
@Show_IDint output, -- 返回的记录内码
@Ret_Msgnvarchar(255) output -- 返回信息
)
AS
BEGIN
DECLARE
@v_iint,
@v_Show_Numint,
@v_Show_IDint
SET @Show_ID=-1;
IF NOT Exists(SELECT News_Code FROM vwNews_Info WHERE News_Code=@News_Code)
BEGIN
SET @Ret_Msg= '不存在指定的新闻编码 ';
RETURN 1;
END
IF Exists(select News_Code FROM News_Show_Control WHERE Sub_System=@Sub_System
and Category_ID=@Category_ID and News_Code=@News_Code)
BEGIN
SET @Ret_Msg= '已经存在该新闻编码 ';
RETURN 2;
END
SELECT @v_Show_Num=Show_Num FROM News_Category WHERE Category_ID=@Category_ID;
SET @v_Show_Num=isnull(@v_Show_Num,0);
IF @v_Show_Num <=0
BEGIN
SELECT @Ret_Msg= '该新闻类别显示控制的条目为0 ';
RETURN 3;
END
DECLARE curNews_Show_Control CURSOR FOR
SELECT Show_ID FROM News_Show_Control
WHERE Sub_System=@Sub_System
and Category_ID=@Category_ID
ORDER BY List_Order;
SET @v_i=1;
OPEN curNews_Show_Control;
FETCH NEXT FROM curNews_Show_Control INTO @v_Show_ID;
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @v_i <@v_Show_Num
UPDATE News_Show_Control SET List_Order=@v_i+1 WHERE Show_ID=@v_Show_ID;
ELSE
DELETE FROM News_Show_Control WHERE Show_ID=@v_Show_ID;
SET @v_i=@v_i+1;
FETCH NEXT FROM curNews_Show_Control INTO @v_Show_ID;
END;
CLOSE curNews_Show_Control;
DEALLOCATE curNews_Show_Control;
INSERT News_Show_Control(Sub_System,Category_ID,News_Code,List_Order,Create_Time)
VALUES(@Sub_System,@Category_ID,@News_Code,1,getdate());
SET @Show_ID=SCOPE_IDENTITY();
SET @Ret_Msg= '执行成功 ';
RETURN 0;
END
GO
主要是改下面的：
其中List_Order 是按照顺序排列的。
希望大家帮忙，谢谢了。。。
[解决办法]
在表中加一个字段,把置顶的贴子设成-1或0不就行了,按升序排列
[解决办法]
...
DECLARE curNews_Show_Control CURSOR FOR
SELECT Show_ID FROM News_Show_Control
WHERE Sub_System=@Sub_System
and Category_ID=@Category_ID
ORDER BY Keep_Top,List_Order;
...
[解决办法]
USE JNews;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N '[dbo].[procNews_Add_Show_Control] ') AND type in (N 'P ', N 'PC '))
DROP PROCEDURE [dbo].[procNews_Add_Show_Control]
GO
CREATE PROCEDURE procNews_Add_Show_Control
(
@Sub_Systemint, -- 子系统
@Category_IDint, -- 新闻分类
@News_Codechar(16), -- 新闻编号
@Keep_Top int,---------修改1 加置顶变量
@Show_IDint output, -- 返回的记录内码
@Ret_Msgnvarchar(255) output -- 返回信息
)
AS
BEGIN
DECLARE
@v_iint,
@v_Show_Numint,
@v_Show_IDint
SET @Show_ID=-1;
IF NOT Exists(SELECT News_Code FROM vwNews_Info WHERE News_Code=@News_Code)
BEGIN
SET @Ret_Msg= '不存在指定的新闻编码 ';
RETURN 1;
END
IF Exists(select News_Code FROM News_Show_Control WHERE Sub_System=@Sub_System
and Category_ID=@Category_ID and News_Code=@News_Code)
BEGIN
SET @Ret_Msg= '已经存在该新闻编码 ';
RETURN 2;
END
SELECT @v_Show_Num=Show_Num FROM News_Category WHERE Category_ID=@Category_ID;
SET @v_Show_Num=isnull(@v_Show_Num,0);
IF @v_Show_Num <=0
BEGIN
SELECT @Ret_Msg= '该新闻类别显示控制的条目为0 ';
RETURN 3;
END
DECLARE curNews_Show_Control CURSOR FOR
SELECT Show_ID FROM News_Show_Control
WHERE Sub_System=@Sub_System
and Category_ID=@Category_ID
ORDER BY Keep_Top desc,List_Order;--修改2 加 "Keep_Top desc, "
SET @v_i=1;
OPEN curNews_Show_Control;
FETCH NEXT FROM curNews_Show_Control INTO @v_Show_ID;
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @v_i <@v_Show_Num
UPDATE News_Show_Control SET List_Order=@v_i+1 WHERE Show_ID=@v_Show_ID;
ELSE
DELETE FROM News_Show_Control WHERE Show_ID=@v_Show_ID;
SET @v_i=@v_i+1;
FETCH NEXT FROM curNews_Show_Control INTO @v_Show_ID;
END;
CLOSE curNews_Show_Control;
DEALLOCATE curNews_Show_Control;
INSERT News_Show_Control(Sub_System,Category_ID,News_Code,List_Order,Create_Time,Keep_Top)--修改3 加 ",Keep_Top "
VALUES(@Sub_System,@Category_ID,@News_Code,1,getdate(),@Keep_Top);--修改4 加 ",@Keep_Top "
SET @Show_ID=SCOPE_IDENTITY();
SET @Ret_Msg= '执行成功 ';
RETURN 0;
END
GO
[解决办法]
还是不对，置顶有专门的操作，改第二处就可以了，象Limpire(昨夜小楼)说的
