小弟写了个存储过程。但是提示我else附近有错误,但是我仔细看了没有发现有错误呀。所以想请求各位大神帮帮忙给我看看那的问题下面是代码。在线等。急
CREATE PROC sp_gzfjb
@id int
AS
declare @fid int
declare @gzrw nvarchar(max)
declare @zxr nvarchar(max)
declare @main_id int
declare @jihuakaishi datetime
declare @jihuajieshu datetime
declare @rwjd nvarchar(max)
declare @ts int
declare @ri datetimeselect @main_id=c2.id,@fid=c6.id,@gzrw=c2.工作任务,@zxr=c2.任务人,@rwjd=c6.任务阶段,@jihuakaishi=c6.计划开始,@jihuajieshu=c6.计划结束 FROM C20120822094542 as c2 INNER JOIN S20120822094606 as c6 ON c2.任务跟中=c6.main_id WHERE c2.id=@idSELECT @ts=DATEDIFF(day,@jihuakaishi,@jihuajieshu)+1 declare @ii int
set @ii=0 while @ii<@ts
BEGIN if (@rwjd='开始' or @rwjd='评估' or @rwjd='工作评估')
ELSE
BEGIN
if(@ii=0)
Select @ri=DATEADD(dd,+1,@jihuakaishi)
ELSE
BEGIN
Select @ri=DATEADD(dd,+1,@ri)
END insert into GZFJ (Main_id,工作名称,父工作ID,工作日期,执行人,分解次数,完成状态) values(@main_id,@gzrw+(ii+1/@ts),@fid,@ri,@zxr,'1','0')
ENDset @ii=@ii+1
END
CREATE PROC sp_gzfjb
@id int
AS
declare @fid int
declare @gzrw nvarchar(max)
declare @zxr nvarchar(max)
declare @main_id int
declare @jihuakaishi datetime
declare @jihuajieshu datetime
declare @rwjd nvarchar(max)
declare @ts int
declare @ri datetimeselect @main_id=c2.id,@fid=c6.id,@gzrw=c2.工作任务,@zxr=c2.任务人,@rwjd=c6.任务阶段,@jihuakaishi=c6.计划开始,@jihuajieshu=c6.计划结束 FROM C20120822094542 as c2 INNER JOIN S20120822094606 as c6 ON c2.任务跟中=c6.main_id WHERE c2.id=@idSELECT @ts=DATEDIFF(day,@jihuakaishi,@jihuajieshu)+1 declare @ii int
set @ii=0 while @ii<@ts
BEGIN if (@rwjd='开始' or @rwjd='评估' or @rwjd='工作评估')
ELSE
BEGIN
if(@ii=0)
Select @ri=DATEADD(dd,+1,@jihuakaishi)
ELSE
BEGIN
Select @ri=DATEADD(dd,+1,@ri)
END insert into GZFJ (Main_id,工作名称,父工作ID,工作日期,执行人,分解次数,完成状态) values(@main_id,@gzrw+(ii+1/@ts),@fid,@ri,@zxr,'1','0')
ENDset @ii=@ii+1
END
@id int
AS
declare @fid int
declare @gzrw nvarchar(max)
declare @zxr nvarchar(max)
declare @main_id int
declare @jihuakaishi datetime
declare @jihuajieshu datetime
declare @rwjd nvarchar(max)
declare @ts int
declare @ri datetimeselect @main_id=c2.id,@fid=c6.id,@gzrw=c2.工作任务,@zxr=c2.任务人,@rwjd=c6.任务阶段,@jihuakaishi=c6.计划开始,@jihuajieshu=c6.计划结束 FROM C20120822094542 as c2 INNER JOIN S20120822094606 as c6 ON c2.任务跟中=c6.main_id WHERE c2.id=@idSELECT @ts=DATEDIFF(day,@jihuakaishi,@jihuajieshu)+1 declare @ii int
set @ii=0 while @ii<@ts
BEGIN if (@rwjd='开始' or @rwjd='评估' or @rwjd='工作评估')
--ELSE --这儿多了个ELSE, 建立楼主在编程时BEGIN END要对齐,层次之间要有缩进,就能减少很多BUG
BEGIN
if(@ii=0)
Select @ri=DATEADD(dd,+1,@jihuakaishi)
ELSE
BEGIN
Select @ri=DATEADD(dd,+1,@ri)
END insert into GZFJ (Main_id,工作名称,父工作ID,工作日期,执行人,分解次数,完成状态) values(@main_id,@gzrw+(ii+1/@ts),@fid,@ri,@zxr,'1','0')
ENDset @ii=@ii+1
END
CREATE PROC sp_gzfjb @id INT
AS
SET NOCOUNT ON --这句话加上,存储过程中没必要显示受影响行数DECLARE @fid INT
DECLARE @gzrw NVARCHAR(MAX)
DECLARE @zxr NVARCHAR(MAX)
DECLARE @main_id INT
DECLARE @jihuakaishi DATETIME
DECLARE @jihuajieshu DATETIME
DECLARE @rwjd NVARCHAR(MAX)
DECLARE @ts INT
DECLARE @ri DATETIMESELECT
@main_id = c2.id ,
@fid = c6.id ,
@gzrw = c2.工作任务 ,
@zxr = c2.任务人 ,
@rwjd = c6.任务阶段 ,
@jihuakaishi = c6.计划开始 ,
@jihuajieshu = c6.计划结束
FROM C20120822094542 AS c2
INNER JOIN S20120822094606 AS c6
ON c2.任务跟中 = c6.main_id
WHERE c2.id = @idSELECT @ts = DATEDIFF(day, @jihuakaishi, @jihuajieshu) + 1 DECLARE @ii INT
SET @ii = 0 WHILE @ii < @ts
BEGIN
IF ( @rwjd = '开始' OR @rwjd = '评估' OR @rwjd = '工作评估')
BEGIN
IF ( @ii = 0 ) --建议:哪怕只有一行SQL,也要加上BEGIN...END...
BEGIN
SELECT @ri = DATEADD(dd, +1, @jihuakaishi)
END
ELSE
BEGIN
SELECT @ri = DATEADD(dd, +1, @ri)
END INSERT INTO GZFJ
(
Main_id ,
工作名称 ,
父工作ID ,
工作日期 ,
执行人 ,
分解次数 ,
完成状态
)
VALUES
(
@main_id ,
@gzrw + ( ii + 1 / @ts ) ,
@fid ,
@ri ,
@zxr ,
'1'
)
ENDSET @ii = @ii + 1
END
SELECT @gzrw = N'我是一个好孩子', @ii=0, @ts = 5
--注意,字符串拼接时,都要转换成字符类型再拼接.(字符+数字 这种方式运算时,SQL SERVER默认会把字符转成数字后再运算,而导致报错)
SELECT @gzrw + '(' + LTRIM(@ii+1) + '/' + LTRIM(@ts) + ')' --LTRIM函数的目的,只是为了把数字转换成字符类型
/*
(无列名)
我是一个好孩子(1/5)
*/
SELECT @gzrw = N'我是一个好孩子', @ii=0, @ts = 5
--注意,字符串拼接时,都要转换成字符类型再拼接.(字符+数字 这种方式运算时,SQL SERVER默认会把字符转成数字后再运算,而导致报错)
SELECT @gzrw + '(' + LTRIM(@ii+1) + '/' + LTRIM(@ts) + ')' --LTRIM函数的目的,只是为了把数字转换成字符类型
/*
(无列名)
我是一个好孩子(1/5)
*/还有一个小问题最后一个就是我用游标,但是作为游标读取这一列中有我不想要的2个我怎么让游标跳过去呀
@id INT,
@name NVARCHAR(10)
DECLARE cursor_name CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
id,
name
FROM dbo.tablename WITH (NOLOCK)
ORDER BY idOPEN cursor_name
FETCH NEXT FROM cursor_name INTO @id, @nameWHILE (@@FETCH_STATUS = 0)
BEGIN
--这儿加个判断,如果数据不是你要的,就不执行代码
IF(你的判断)
BEGIN
/* your sql commands */
END FETCH NEXT FROM cursor_name INTO @id, @name
ENDCLOSE cursor_name
DEALLOCATE cursor_name