不解释了，看实际SQL
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P_DraftNew]
(
@SourceId int,
@StyleId int,
@ThemeId int,
@ResTypeId int,
@ResLength int,
@Title varchar(200),
@OtherSource varchar(200),
@Years datetime,
@Author varchar(100),
@UserId int,
@CreateDate datetime,
@LibLevel int,
@FileType1 int,
@Hashcode1 varchar(1024),
@FilePath1 varchar(100),
@FileType2 int,
@Hashcode2 varchar(1024),
@FilePath2 varchar(100),
--@OriginResId", SqlDbType.Int,4),
--@ResTypeId", SqlDbType.Int,4),
@BeginTime decimal,
@EndTime decimal,
@ResText varchar(2000),
@Readability VarChar(200),
--@LibLevel", SqlDbType.Int,4),
@KeyWord VarChar(100),
@DiffLevel Int,
--@UserId", SqlDbType.Int,4),
--@CreateDate", SqlDbType.DateTime),
@Remark VarChar(2000),
@UseTimes Int,
--@LastUseDate DateTime,
@PlayBeginTime Decimal,
@PlayEndTime Decimal,
--@FileId", SqlDbType.Int,4),
@Status Int,
@IncludeAnchor Bit,
@CurrentOperatorId Int
)
AS
declare @Res_OriginId int;
declare @Res_FileId int;
declare @ResId int
BEGIN
SET NOCOUNT ON;
--@@开始添加资源
--添加1
insert into Res_OriginInfo(SourceId,StyleId,ThemeId,ResTypeId,ResLength,Title,OtherSource,Years,
Author,UserId,CreateDate,LibLevel) values ( @SourceId,@StyleId,@ThemeId,@ResTypeId,
@ResLength,@Title,@OtherSource,@Years,@Author,@UserId,getdate(),@LibLevel );
--得到ID
set @Res_OriginId = @@IDENTITY
--添加2
if @FilePath1 <> ''
begin
insert into Res_File(OriginResId,FileType,Hashcode,FilePath)
values (@Res_OriginId,@FileType1,@Hashcode1,@FilePath1);
if @FileType1 = 0
begin
set @Res_FileId = @@IDENTITY
end
end
if @FilePath2 <> ''
begin
insert into Res_File(OriginResId,FileType,Hashcode,FilePath)
values (@Res_OriginId,@FileType2,@Hashcode2,@FilePath2);
if @FileType2 = 0
begin
set @Res_FileId = @@IDENTITY
end
end
--添加3
insert into Res_Info(OriginResId,ResTypeId,BeginTime,EndTime,ResText,Readability,LibLevel,
KeyWord,DiffLevel,UserId,CreateDate,Remark,UseTimes,LastUseDate,PlayBeginTime,
PlayEndTime,FileId,Status,IncludeAnchor,CurrentOperatorId)
select
@Res_OriginId,@ResTypeId,@BeginTime,@EndTime,@ResText,@Readability,@LibLevel,
@KeyWord,@DiffLevel,@UserId,getdate(),@Remark,@UseTimes,getdate(),@PlayBeginTime,
@PlayEndTime,@Res_FileId,@Status,@IncludeAnchor,@CurrentOperatorId;
set @ResId = @@IDENTITY
--@@资源添加完毕
--@@开始为该资源添加默认的命题记录
-- PS:
-- 1：Res_Info表
-- 2：根据ResId在Res_QTypeGroup找到GroupId，这个表是用来绑定命题的
-- 3：根据GroupId，在Question_Group_Type中找到QuestionTypeId，这个表示是复合题题型名称
-- ==
-- 1：添加一条记录，首先在Res_Info中添加一条记录；
-- 2：根据ResId在Res_QTypeGroup找到GroupId，有可能存在多个GroupId，然后根据
-- GroupId，再在Question_Complex_Group添加对应的记录；
-- 3：根据ResId在Res_QTypeGroup找到GroupId，根据GroupId，在Question_Group_Type，
-- 找到该GoupId对应的复合题题型组合，再去相应的业务表去添加记录。
-- PS：发现根本不用这么复杂，直接根据ResTypeId在Question_Type中找到具体题型
-- 4：真正命题的时候只要去Update各业务表就行。
---- 负责插入Question_Complex_Group
declare @GroupId int
declare @QuestionGroupIdTemp int
declare @QuestionTypeIdTemp int
declare @ComplexIdTemp int
declare @PropCount int
declare @TypeBlockIdTemp int
declare @PropCountTemp int
declare @QSBlockIdTemp int
declare @BaseTypeIdTemp int
declare @QuestionIdTemp int
set @PropCount=0
declare group_cursor cursor for
select GroupId from Res_QTypeGroup where ResTypeId=@ResTypeId
open group_cursor
fetch next from group_cursor
into @GroupId
while @@FETCH_STATUS = 0
begin
insert into Question_Complex_Group (ResId, GroupId, UserId,
UserName, RefreshDate, Remark, AccountID, IsAssigned, Status,
CurrentOperatorId, CreateDate, IsNeedRecord,
IsChecked) values (@ResId, @GroupId, @UserId, @Author, getdate(),
'', 0, 1, 0, @UserId,getdate(), 1, 1)
set @QuestionGroupIdTemp = @@IDENTITY
--此处开始第二个游标
declare Group_Type_Cursor cursor for
select QuestionTypeId from Question_Group_Type where GroupId=@GroupId
open Group_Type_Cursor
fetch next from Group_Type_Cursor
into @QuestionTypeIdTemp
while @@FETCH_STATUS = 0
begin
--插入记录到Question_Complex
insert into Question_Complex (QuestionTypeId, Description, Glossary,ComplexTitle,
OriginResId, ResId, QuestionGroupId,IsAnchor, IsNew) values
(@QuestionTypeIdTemp, '', '', '', @Res_OriginId, @ResId, @QuestionGroupIdTemp, 0, 1)
set @ComplexIdTemp = @@IDENTITY
--计算每个题型需要命多少题
declare TypeBlock_Cursor CURSOR FOR
select TypeBlockId from Question_Type_Block where QuestionTypeId=@QuestionTypeIdTemp and ResTypeId=@ResTypeId
open TypeBlock_Cursor
fetch next from TypeBlock_Cursor
into @TypeBlockIdTemp
while @@FETCH_STATUS = 0
begin
select @PropCountTemp=MaxCount, @BaseTypeIdTemp=BaseTypeId from Question_Type_List where TypeBlockId=@TypeBlockIdTemp
--对题数进行累加
set @PropCount=@PropCount+@PropCountTemp
--插入记录到Question_Complex_Block，一个BlockType，在这个表中就要对应一条记录
insert into Question_Complex_Block(ComplexId, IsAll, BeginTime, EndTime) values
(@ComplexIdTemp, 1, 0, 0)
set @QSBlockIdTemp=@@IDENTITY
--开始分类别进行插入Complex_Info，ChoiceVice等表，要根据题型的不同插入的内容也不同
--@PropCountTemp为几，就在Complex_Info插入几条记录
while @PropCountTemp > 0
begin
insert into Question_Info (ComplexId, BaseTypeId, QSBlockId, QuestionTitle,
JudgeAnswer, CtrlId, LinkInfo, StandardAnswer, FillAnswer, ReadRecordId)
values (@ComplexIdTemp, @BaseTypeIdTemp, @QSBlockIdTemp, '默认题面',
0,'','','','','')
set @QuestionIdTemp=@@IDENTITY
----音频短篇选择0，音频中篇选择1，视频选择4，仔细阅读7，快速阅读8，音频短篇(六级)9，
----音频中篇对话(六级)10，音频中篇(六级)11，视频选择(六级)12，快速阅读(六级)15，
----仔细阅读(六级)16，还要插入插入一个附表ChoiceVice
if @BaseTypeIdTemp<2
begin
insert into Question_ChoiceVice (QuestionId, ChoiceTitle,IsAnswer)
values (@QuestionIdTemp, '选项1', 1)
insert into Question_ChoiceVice (QuestionId, ChoiceTitle,IsAnswer)
values (@QuestionIdTemp, '选项2', 0)
insert into Question_ChoiceVice (QuestionId, ChoiceTitle,IsAnswer)
values (@QuestionIdTemp, '选项3', 0)
insert into Question_ChoiceVice (QuestionId, ChoiceTitle,IsAnswer)
values (@QuestionIdTemp, '选项4', 0)
end
set @PropCountTemp=@PropCountTemp-1
end
--end 分类别进行插入
fetch next from TypeBlock_Cursor
into @TypeBlockIdTemp
end
close TypeBlock_Cursor
deallocate TypeBlock_Cursor
--计算结束
--插入记录到Question_Complex_Validate
insert into Question_Complex_Validate(ComplexId, MsgUndone, PropCount, FinishedCount,
IsPropDone) values (@ComplexIdTemp, '尚未命题', @PropCount, 0, 0)
set @PropCount=0
fetch next from Group_Type_Cursor
into @QuestionTypeIdTemp
end
close Group_Type_Cursor
deallocate Group_Type_Cursor
--end 第二个游标
fetch next from group_cursor
into @GroupId
end
close group_cursor
deallocate group_cursor
----end负责插入Question_Complex_Group
--@@命题记录添加完毕
end
联系客服
