邹建大哥考试系统存储过程,我想加多一个身份证号,但出错了,该怎么改?

[wnsr]

邹建大哥考试系统存储过程,我想加多一个身份证号,但出错了,该怎么改?
--题库表结构
CREATE TABLE tb(
ID int PRIMARY KEY, --题目ID
Type int, --题型
col1 varchar(10)) --其他需要的字段
INSERT tb SELECT 1,1,'试题1'
UNION ALL SELECT 2,1,'试题2'
UNION ALL SELECT 3,3,'试题3'
UNION ALL SELECT 4,3,'试题4'
UNION ALL SELECT 5,3,'试题5'
UNION ALL SELECT 6,3,'试题6'
GO

--生成试卷的处理过程
CREATE PROC p_test
@试卷份数 int,
@试题数 varchar(100),
@身份证 varchar(100)
AS
SET NOCOUNT ON
--参数检测
IF ISNULL(@试卷份数,0)<1 RETURN
IF ISNULL(@试题数,'')='' RETURN

--分拆题型
DECLARE @i int,@Type int
SET @i=LEN(@试题数)
SET ROWCOUNT @i
SELECT ID=IDENTITY(int) INTO # FROM sysobjects a,sysobjects b
SET ROWCOUNT 0
CREATE TABLE #type(Type int,Nums int)
INSERT #type(Type,Nums)
SELECT LEFT(s,CHARINDEX(':',s+':')-1),STUFF(s,1,CHARINDEX(':',s+':'),'')
FROM(
SELECT s=SUBSTRING(@试题数,ID,CHARINDEX(',',@试题数+',',ID)-ID)
FROM #
WHERE SUBSTRING(','+@试题数,ID,1)=','
)a WHERE ISNUMERIC(LEFT(s,CHARINDEX(':',s+':')-1))=1
AND ISNUMERIC(STUFF(s,1,CHARINDEX(':',s+':'),''))=1
AND STUFF(s,1,CHARINDEX(':',s+':'),'')>0
AND EXISTS(
SELECT * FROM tb WHERE Type=LEFT(s,CHARINDEX(':',s+':')-1))

--使用游标,为每种题型随机抽取题目
CREATE TABLE t(gID int IDentity,type int,ID int,sfzh varchar(100) null)
DECLARE tb CURSOR LOCAL
FOR
SELECT Type,Nums*@试卷份数 FROM #Type
OPEN tb
FETCH tb INTO @Type,@i
WHILE @@FETCH_STATUS=0
BEGIN
--为每个题型随机抽取题目
SET ROWCOUNT @i
INSERT t(Type,ID,sfzh varchar(100)) SELECT tb.@Type,tb.ID,@身份证 FROM tb
WHERE Type=@Type ORDER BY NEWID()
SET @i=@i-@@ROWCOUNT
WHILE @i>0
BEGIN
SET ROWCOUNT @i
INSERT t(Type,ID,sfzh varchar(100)) SELECT tb,@Type,tb.ID,@身份证 FROM tb
WHERE Type=@Type ORDER BY NEWID()
SET @i=@i-@@ROWCOUNT
END
FETCH tb INTO @Type,@i
END
CLOSE tb
DEALLOCATE tb

--显示结果
SET ROWCOUNT 0
SELECT 试卷编号=(b.gid-b1.gid)/c.Nums+1
,a.*,b.sfzh
FROM tb a,t b,#type c
,(SELECT Type,gid=MIN(gid) FROM #t GROUP BY Type)b1
WHERE a.ID=b.ID
AND b.Type=c.Type
AND b.Type=b1.Type
ORDER BY 试卷编号,b.gid
GO

--调用
EXEC p_test 2,'1:1,3:2','430223900101411'

就提示 INSERT t(Type,ID,sfzh varchar(100)) SELECT tb.@Type,tb.ID,@身份证 FROM tb
有语法错误,该怎么改啊

[开着拖拉机泡妞]

INSERT t(Type,ID,sfzh去掉类型) SELECT tb.@Type,tb.ID,@身份证 FROM tb  

[wnsr]

--题库表结构
CREATE TABLE tb(
ID int PRIMARY KEY, --题目ID
Type int, --题型
col1 varchar(10)) --其他需要的字段
INSERT tb SELECT 1,1,'试题1'
UNION ALL SELECT 2,1,'试题2'
UNION ALL SELECT 3,3,'试题3'
UNION ALL SELECT 4,3,'试题4'
UNION ALL SELECT 5,3,'试题5'
UNION ALL SELECT 6,3,'试题6'
GO

--生成试卷的处理过程
CREATE PROC p_test
@试题数 varchar(100),
@身份证 varchar(100)
AS
SET NOCOUNT ON
--参数检测
IF ISNULL(@试题数,'')='' RETURN

--分拆题型
DECLARE @i int,@Type int
SET @i=LEN(@试题数)
SET ROWCOUNT @i
SELECT ID=IDENTITY(int) INTO # FROM sysobjects a,sysobjects b
SET ROWCOUNT 0
CREATE TABLE #type(Type int,Nums int,sfzh varchar(100) null)
INSERT #type(Type,Nums)
SELECT LEFT(s,CHARINDEX(':',s+':')-1),STUFF(s,1,CHARINDEX(':',s+':'),'')
FROM(
SELECT s=SUBSTRING(@试题数,ID,CHARINDEX(',',@试题数+',',ID)-ID)
FROM #
WHERE SUBSTRING(','+@试题数,ID,1)=','
)a WHERE ISNUMERIC(LEFT(s,CHARINDEX(':',s+':')-1))=1
AND ISNUMERIC(STUFF(s,1,CHARINDEX(':',s+':'),''))=1
AND STUFF(s,1,CHARINDEX(':',s+':'),'')>0
AND EXISTS(
SELECT * FROM tb WHERE Type=LEFT(s,CHARINDEX(':',s+':')-1))

--使用游标,为每种题型随机抽取题目
CREATE TABLE t(gID int IDentity,type int,ID int,col11 varchar(10),sfzh varchar(100) null)
DECLARE tb CURSOR LOCAL
FOR
SELECT Type,Nums FROM #Type
OPEN tb
FETCH tb INTO @Type,@i
WHILE @@FETCH_STATUS=0
BEGIN
--为每个题型随机抽取题目
SET ROWCOUNT @i
INSERT t(Type,ID,col11,sfzh ) SELECT @Type,tb.ID,tb.col1,@身份证 FROM tb
WHERE Type=@Type ORDER BY NEWID()
SET @i=@i-@@ROWCOUNT
WHILE @i>0
BEGIN
SET ROWCOUNT @i
INSERT t(Type,ID,col11,sfzh ) SELECT @Type,tb.ID,tb.col1,@身份证 FROM tb
WHERE Type=@Type ORDER BY NEWID()
SET @i=@i-@@ROWCOUNT
END

FETCH tb INTO @Type,@i
END
CLOSE tb
DEALLOCATE tb

--显示结果
select*from t where sfzh=@身份证
GO

--调用
EXEC p_test '1:1,3:2','430223900101411'

结果为
gid type id col11 sfzh
1 1 1 试题1 430223900101411
2 3 3 试题3 430223900101411

改成这样,试卷份数不要,明明直接打开t表有3条数据,为什么显示只有两条啊?

[wnsr]

忘记说清楚,它的输入参数,@试题数为字符型,包含了每种题型及每种题型要选取的题目数,例如'1:1,3:2'表示试卷包含题型为1的1个题目\题型为3的2个题目,共计3个题目

[wnsr]

呵呵,前面加SET ROWCOUNT 0就可以了,
顺便问问我现在用的是Sqlserver2000,请问sqlserver2005也可以用SET ROWCOUNT吧?
另外,存储过程中使用了临时表,不同的客户机的客户端调用应该没问题吧?