BEGINDECLARE id int(4);
DECLARE questionType char(50); ------用来表示要查询表的名称SELECT QuestionTypeName INTO questionType FROM question_types WHERE question_types.QuestionTypeId = questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到
INSERT INTO questionType (Question, Answer) VALUES (question, answer);----问题就在这里,我运行数据库报错说没有questionType这张表,可是我已经给questionType赋值了啊
SET id = last_insert_id();
INSERT INTO questions (QuestionType, Id, Point, Level) VALUES (questionTypeId, id, point, level);
END
DECLARE questionType char(50); ------用来表示要查询表的名称SELECT QuestionTypeName INTO questionType FROM question_types WHERE question_types.QuestionTypeId = questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到
INSERT INTO questionType (Question, Answer) VALUES (question, answer);----问题就在这里,我运行数据库报错说没有questionType这张表,可是我已经给questionType赋值了啊
SET id = last_insert_id();
INSERT INTO questions (QuestionType, Id, Point, Level) VALUES (questionTypeId, id, point, level);
END
as
--...
begin
declare @sqlstr varchar(8000)
declare @tablename varchar(100)
--...
SELECT @tablename=QuestionTypeName FROM question_types WHERE question_types.QuestionTypeId = questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到
--...
set @sqlstr='INSERT INTO '+@tablename+' (Question, Answer) VALUES (question, answer)' exec(@sqlstr)
end
as
--...
begin
declare @sqlstr varchar(8000)
declare @tablename varchar(100)
--...
SELECT @tablename=QuestionTypeName FROM question_types WHERE question_types.QuestionTypeId = questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到
--...
set @sqlstr='INSERT INTO '+@tablename+' (Question, Answer) VALUES (question, answer)' exec(@sqlstr)--... 剩下的自己处理吧,用动态语句end
1.动态创建表需要使用动态SQL使用 exec方式执行,
2.select into 方式创建的表的列和你选择的列是一致
而你只选择了一列QuestionTypeName,
而你后面却写入了两列INSERT INTO questionType (Question, Answer).
不知道你想要什么样的。
--try
BEGIN
DECLARE @id int(4);
DECLARE @questionType sysname; ------用来表示要查询表的名称 SELECT @questionType = QuestionTypeName FROM question_types WHERE question_types.QuestionTypeId = @questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到
exec('INSERT INTO ['+ltrim(@questionType)+'] (Question, Answer) VALUES ('+@question+', '+@answer+');')----问题就在这里,我运行数据库报错说没有questionType这张表,可是我已经给questionType赋值了啊
SET @id = last_insert_id();
INSERT INTO questions ([QuestionType], [Id], [Point], [Level]) VALUES (@questionTypeId, @id, @point, @level);
END
DECLARE @id int(4);
DECLARE @questionType sysname;------用来表示要查询表的名称
SELECT @questionType=QuestionTypeName FROM question_types WHERE question_types.QuestionTypeId = questionTypeId; --------给questionType赋值,根据输入参数questionTypeId从qustion_types表中得到 Exec(N'INSERT INTO ['+@questionType+N'](Question, Answer) select '+@question+N','+@answer+N'');----问题就在这里,我运行数据库报错说没有questionType这张表,可是我已经给questionType赋值了啊
SET @id = last_insert_id();
INSERT INTO questions ([QuestionType], [Id], [Point], [Level]) select @questionTypeId, @id, @point, @level;
END