Question

SqlServer存储过程

表T_CPlan
主键 计划名 创建时间 循环时间
T_CPlan_ID T_Plan_Name CreateTime CycleTime
1 周计划 2012-2-17 7

表T_Plan
主键 计划名 父计划 创建时间
T_CPlan_ID T_Plan_Name T_CPlan_ID CreateTime
1 周计划 1 2012-2-17
2 周计划 1 2012-2-24

表T_Plan是子表，使用存储过程根据表T_CPlan生成
SqlServer存储过程该怎么写

Answer 1

create procedure prCreateSubPlan
as
begin
declare @id int,
@intCycle int,
@planName varchar(100),
@createTime smalldatetime,
@cycleTime int

select @id = min(t_cplan_id) from t_cplan

while (@id is not null)
begin
select @planName=t_plan_name, @createTime = createTime, @cycleTime = cycleTime from t_cplan where t_cplan_id=@id

select @intCycle= 0
while (@intCycle<@cycleTime)
begin
-- 表t_plan 列t_plan_id是IDENTITY 列
insert t_plan (t_plan_name, t_cplan_id, createTime)
values (@planName, @id, dateadd(day, @intCycle, @createTime))
select @intCycle = @intCycle + 1
end
select @id = min(t_cplan_id) from t_cplan where t_cplan_id>@id
end
end
go

Answer 2

CREATE PROCEDURE sp_CreateSubPlan
@CPlanID INT
AS
BEGIN
DECLARE @CycleTime INT
DECLARE @T_PlanName NVARCHAR(10)
DECLARE @BeginDate DATETIME
DECLARE @intTem INT

SELECT @CycleTime=CycleTime,@T_PlanName=T_Plan_Name,@BeginDate=CreateTime,@intTem=1 FROM T_CPlan WHERE T_CPlan_ID=@CPlanID

WHILE (@intTem<=@CycleTime)
BEGIN
INSERT INTO T_Plan(T_Plan_Name,T_CPlan_ID,CreateTime)
SELECT @T_PlanName,@CPlanID,DATEADD(WEEK,@intTem-1,@BeginDate)
SELECT @intTem=@intTem+1
END
END
GO

--执行
sp_CreateSubPlan 1
输入的参数为T_CPlan_ID的值

Answer 3

USE tpbaidu

GO

DROP TABLE T_CPLAN;

CREATE TABLE T_CPlan(

T_CPlan_ID INT  PRIMARY KEY,

T_Plan_Name CHAR(20),

CreateTime  DATETIME

)

INSERT INTO T_CPlan VALUES(1,'周计划','2012-2-17')

INSERT INTO T_CPlan VALUES(2,'周计划','2012-2-24')

----存储过程如下

CREATE  PROCEDURE  PROTT

AS

BEGIN

DECLARE @T_CPlan_ID INT,@CREATETIME1 DATETIME,@CREATETIME2 DATETIME,@T_CPLAN_ID2 INT;

SET @T_CPLAN_ID=1;

SELECT @CREATETIME1=CREATETIME FROM T_CPlan WHERE T_CPLAN_ID=@T_CPLAN_ID;

SET @T_CPLAN_ID2=@T_CPLAN_ID+1;

SELECT @CREATETIME2=CREATETIME FROM T_CPlan WHERE T_CPLAN_ID=@T_CPLAN_ID2;

IF exists (SELECT * FROM T_CPlan WHERE T_CPLAN_ID=@T_CPLAN_ID2)

BEGIN

DROP TABLE T_PLAN;

CREATE TABLE T_PLAN(

T_Plan_ID INT PRIMARY KEY,

T_Plan_Name CHAR(8),

CreateTime  DATETIME,

CycleTime   INT

)

DECLARE @CycleTime INT;

SET @CycleTime=CONVERT(INT,@CREATETIME2)-CONVERT(INT,@CREATETIME1);

INSERT INTO T_PLAN VALUES(@T_CPLAN_ID,'周计划',@CREATETIME1,@CycleTime);

SELECT * FROM T_PLAN;

SELECT * FROM T_CPLAN;

END

END

结果如下：

Answer 4

可以用hibernate来做 无需手动建表 只要配类映射文件就行 自动导表