今天同事让我修改他以前写的动弹生成表以及字段的存储过程,因为以前只有3中选项类型,
现在7种类型,所以要重新生成答案表。所以这个小任务就落在我身上了。。呵呵。
其实问题也简单了。因为他以前已经写了我只要加个判断就可以了。。。
所以废话不多说了。
贴代码先。备忘。
USE [Qn]
GO
/******
对象: StoredProcedure [dbo].[CreateTable]
脚本日期: 05/05/2008 08:40:45
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[CreateTable]
AS
BEGIN
declare @sid varchar(10);
declare @qd int;
declare @qt int;
declare @i int declare @j int;
declare @str varchar(max);
declare @num int;
declare @createtable varchar(max)
set @num=0 select top 1 @sid=SID from Questionnaire order by SID desc
IF not EXISTS (SELECT name FROM sysobjects WHERE name ='answer'+@sid )
begin --begin0开始
/*----------创建表和非题目字段-----------------*/
set @createtable= 'create table answer'+@sid+' (AID int identity(1,1) primary key,
UID varchar(MAX), ADateTime datetime)' exec(@createtable)
/*----------创建表和非题目字段结束-----------------*/
declare cursor_find cursor for select QID,Qtype from Question where SID=@sid
open cursor_find fetch cursor_find into @qd,@qt while @@fetch_status=0
begin
------循环遍历每条记录begin1开始
set @num=@num+1 if(@qt=5)
---对多选进行处理begin2开始
begin
set @j=1 select @i=count(*) from [Option] where QID=@qd while(@j<=@i)
begin
---解决多选的选项 begin3开始
set @createtable='alter table answer'+@sid+' add Q'+convert(varchar(50),@num)+'0'+convert(varchar(50),@j)+' varchar(max)' exec( @createtable)
--print 'Q'+convert(varchar(50),@num)+'0'+convert(varchar(50),@j)
set
@j=@j+1
end
---begin3结束
end
----begin2结束
else if(@qt=6)
---对多选加文本的处理
begin
set @j=1 select @i=count(*) from [Option] where QID=@qd while(@j<=@i+1)
begin
if(@j=@i+1)
begin
set @createtable='alter table answer'+@sid+' add Q'+'txt'+convert(varchar(50),@num)+' text' exec( @createtable)
end
else
begin
set @createtable='alter table answer'+@sid+' add Q'+convert(varchar(50),@num)+'0'+convert(varchar(50),@j)+' varchar(max)'
exec( @createtable)
--print 'Q'+convert(varchar(50),@num)+'0'+convert(varchar(50),@j)
end
set
@j=@j+1 end end else if(@qt=4)
---对单选加文本的处理
begin
---添加代码
set @createtable='alter table answer'+@sid+' add Q'+convert(varchar(50),@num)+' varchar(max)'
exec( @createtable)
set
@createtable='alter table answer'+@sid+' add Q'+'txt'+convert(varchar(50),@num)+' text'
exec( @createtable)
end
else --处理非多选项begin4开始
begin
set @createtable='alter table answer'+@sid+' add Q'+convert(varchar(50),@num)+' varchar(max)' exec( @createtable)
end
---begin4结束
fetch cursor_find into @qd,@qt
end
---begin1结束
close cursor_find deallocate cursor_find
end
--begin0结束
END