表名:tab1
——————————
prodID
1
2
3
5兄弟我想写个数据插入存储过程,在“3”和“5”之间插入“4”,实现如下:
表名:tab1
——————————
prodID
1
2
3
4
5
我是这样写的,不知道哪出错了。请指教
------------------------------------
--用途:增加一条记录
--项目名称:
--说明:
--时间:2006-9-1 15:44:34
------------------------------------
CREATE PROCEDURE Prod_ADD
@ProdID char(10) ,
@QYID char(7) ,
@ProdName varchar(80) ,
@TuPian image ,
@ProdMiaoShu text ,
@BaoJia varchar(40)
AS
begin tran
declare @id char(10)
declare @autoID int
set @id=0
set @autoID=1
select @id=ProdID from Prod where ProdID =@ProdID
if @id=0
begin
INSERT INTO Prod(
[ProdID],[QYID],[ProdName],[photo],[ProdMiaoShu],[BaoJia]
)VALUES(
@ProdID,@QYID,@ProdName,@TuPian,@ProdMiaoShu,@BaoJia
)
end
else
begin
WHILE (select @id=[ProdID] from Prod where ProdID=@autoID)< CAST(@ProdID AS int)
begin
if @id=0
begin
INSERT INTO Prod(
[ProdID],[QYID],[ProdName],[photo],[ProdMiaoShu],[BaoJia]
)VALUES(
@autoID,@QYID,@ProdName,@TuPian,@ProdMiaoShu,@BaoJia
)
BREAK
end
ELSE
begin
set @autoID=@autoID+1;
CONTINUE
end
end
end
if @@error!=0
begin
rollback
end
else
begin
commit
end
GO
——————————
prodID
1
2
3
5兄弟我想写个数据插入存储过程,在“3”和“5”之间插入“4”,实现如下:
表名:tab1
——————————
prodID
1
2
3
4
5
我是这样写的,不知道哪出错了。请指教
------------------------------------
--用途:增加一条记录
--项目名称:
--说明:
--时间:2006-9-1 15:44:34
------------------------------------
CREATE PROCEDURE Prod_ADD
@ProdID char(10) ,
@QYID char(7) ,
@ProdName varchar(80) ,
@TuPian image ,
@ProdMiaoShu text ,
@BaoJia varchar(40)
AS
begin tran
declare @id char(10)
declare @autoID int
set @id=0
set @autoID=1
select @id=ProdID from Prod where ProdID =@ProdID
if @id=0
begin
INSERT INTO Prod(
[ProdID],[QYID],[ProdName],[photo],[ProdMiaoShu],[BaoJia]
)VALUES(
@ProdID,@QYID,@ProdName,@TuPian,@ProdMiaoShu,@BaoJia
)
end
else
begin
WHILE (select @id=[ProdID] from Prod where ProdID=@autoID)< CAST(@ProdID AS int)
begin
if @id=0
begin
INSERT INTO Prod(
[ProdID],[QYID],[ProdName],[photo],[ProdMiaoShu],[BaoJia]
)VALUES(
@autoID,@QYID,@ProdName,@TuPian,@ProdMiaoShu,@BaoJia
)
BREAK
end
ELSE
begin
set @autoID=@autoID+1;
CONTINUE
end
end
end
if @@error!=0
begin
rollback
end
else
begin
commit
end
GO
解决方案 »
- 请教一下,关于mssql数据库2000的一个问题,
- 数据库DBA面试失败
- sql server系统视图sys.objects和sys.all_objects有什么区别?
- sql server启动问题!!!!!
- 以........开头的 标识符 太长。最大长度为 128怎么办?
- vb ado中的sql语句问题?
- 汉字所占字符
- 一个SQLSERVER2000的问题!!!!!!!
- 求助!
- ~{TZ4f~}儲過~{3LVPTu~}樣進~{PP~}錯誤轉~{RF~}
- 菜鸟求SQL身份认证用户名和密码
- 安了SP3补丁后,为什么显示是SP2?
插入