sql存储过程

u011081520 2013-08-20 03:52:55

写个存储过程，，到exec运行的时候为啥来提示。命令已完成？ALTER PROC sp_gzfjjs
@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 datetime
declare @gzid datetime

SELECT @main_id=c2.id,@fid=c0.id,@gzrw=c2.项目名称,@rwjd=c0.项目状态,@zxr=c0.完成人,@jihuakaishi=c0.完成开始时间,@jihuajieshu=c0.完成结束时间 from C20120810145122 as c2 INNER join S20120810145230 as c0 ON c2.项目跟踪=c0.main_id WHERE c2.id=@id
SELECT @gzid=Main_id from GZFJ
if(@gzid <> @id)
BEGIN
declare @name varchar(50)
declare t_cursor cursor for
SELECT c0.项目状态 from C20120810145122 as c2 INNER join S20120810145230 as c0 ON c2.项目跟踪=c0.main_id WHERE c2.id=@id AND c0.项目状态 <> '开始' AND c0.项目状态 <> '评估' AND c0.项目状态 <> '工作评估'
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
SELECT @jihuakaishi=c0.完成开始时间,@jihuajieshu=c0.完成结束时间 from C20120810145122 as c2 INNER join S20120810145230 as c0 ON c2.项目跟踪=c0.main_id WHERE c2.id=@id AND c0.项目状态=@name
SELECT @ts=DATEDIFF(day,@jihuakaishi,@jihuajieshu)+1
SELECT @fid=c0.id from C20120810145122 as c2 INNER join S20120810145230 as c0 ON c2.项目跟踪=c0.main_id WHERE c2.id=82 AND c0.项目状态 <> '开始' AND c0.项目状态 <> '评估' AND c0.项目状态 <> '工作评估' AND c0.项目状态=@name
declare @ii int
set @ii=0
while @ii<@ts
BEGIN

if (@name <> '开始' or @name <> '评估' or @name <> '工作评估')
BEGIN
if(@ii=0)
BEGIN
Select @ri=DATEADD(dd,+0,@jihuakaishi)
END
ELSE
BEGIN
Select @ri=DATEADD(dd,+1,@ri)
END
insert into GZFJ (Main_id,工作名称,父工作ID,工作日期,执行人,分解次数,完成状态,LX,阶段名称) values(@main_id,@name + '（' + LTRIM(@ii+1) + '/' + LTRIM(@ts) + '）',@fid,@ri,@zxr,'1','0','技术',@name)
END

set @ii=@ii+1
END
fetch next from t_cursor into @name
if @@error!=0
begin
rollback tran
return
end
end
close t_cursor
deallocate t_cursor
END
ELSE
BEGIN
END

...全文

43 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

Shawn 2013-08-20

打赏
举报

--调用时. 如果正常执行，默认返回0.还有，调用存储过程时，外层代码加上异常处理
declare @rv int
exec @rv = sp_gzfjjs 1
select @rv

--存储过程中存在一些问题
CREATE PROC sp_gzfjjs @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 DATETIME
    DECLARE @gzid DATETIME

    SELECT  @main_id = c2.id ,
            @fid = c0.id ,
            @gzrw = c2.项目名称 ,
            @rwjd = c0.项目状态 ,
            @zxr = c0.完成人 ,
            @jihuakaishi = c0.完成开始时间 ,
            @jihuajieshu = c0.完成结束时间
    FROM    C20120810145122 AS c2
            INNER JOIN S20120810145230 AS c0 ON c2.项目跟踪 = c0.main_id
    WHERE   c2.id = @id
	
    SELECT  @gzid = Main_id
    FROM    GZFJ
	
    IF ( @gzid <> @id )   --楼主这儿是datetime类型和整型比,确定这样吗？
        BEGIN
            DECLARE @name VARCHAR(50)
            DECLARE t_cursor CURSOR
            FOR
                SELECT  c0.项目状态
                FROM    C20120810145122 AS c2
                        INNER JOIN S20120810145230 AS c0 ON c2.项目跟踪 = c0.main_id
                WHERE   c2.id = @id
                        AND c0.项目状态 <> '开始'
                        AND c0.项目状态 <> '评估'
                        AND c0.项目状态 <> '工作评估'
            OPEN t_cursor
            FETCH NEXT FROM t_cursor INTO @name
            WHILE @@fetch_status = 0 
                BEGIN
                    SELECT  @jihuakaishi = c0.完成开始时间 ,
                            @jihuajieshu = c0.完成结束时间
                    FROM    C20120810145122 AS c2
                            INNER JOIN S20120810145230 AS c0 ON c2.项目跟踪 = c0.main_id
                    WHERE   c2.id = @id
                            AND c0.项目状态 = @name
                    SELECT  @ts = DATEDIFF(day, @jihuakaishi, @jihuajieshu)
                            + 1 
                    SELECT  @fid = c0.id
                    FROM    C20120810145122 AS c2
                            INNER JOIN S20120810145230 AS c0 ON c2.项目跟踪 = c0.main_id
                    WHERE   c2.id = 82
                            AND c0.项目状态 <> '开始'
                            AND c0.项目状态 <> '评估'
                            AND c0.项目状态 <> '工作评估'
                            AND c0.项目状态 = @name
                    DECLARE @ii INT 
                    SET @ii = 0 
                    WHILE @ii < @ts 
                        BEGIN

                            IF ( @name <> '开始'
                                 OR @name <> '评估'
                                 OR @name <> '工作评估'
                               ) 
                                BEGIN
                                    IF ( @ii = 0 ) 
                                        BEGIN
                                            SELECT  @ri = DATEADD(dd, +0, @jihuakaishi)
                                        END
                                    ELSE 
                                        BEGIN
                                            SELECT  @ri = DATEADD(dd, +1, @ri)
                                        END
                                    INSERT  INTO GZFJ
                                            ( Main_id ,
                                              工作名称 ,
                                              父工作ID ,
                                              工作日期 ,
                                              执行人 ,
                                              分解次数 ,
                                              完成状态 ,
                                              LX ,
                                              阶段名称
                                            )
                                    VALUES  ( @main_id ,
                                              @name + '（' + LTRIM(@ii + 1)
                                              + '/' + LTRIM(@ts) + '）' ,
                                              @fid ,
                                              @ri ,
                                              @zxr ,
                                              '1' ,
                                              '0' ,
                                              '技术' ,
                                              @name
                                            ) 
                                END

                            SET @ii = @ii + 1 
                        END
                    FETCH NEXT FROM t_cursor INTO @name 
                    --IF @@error != 0 		--把这几行去掉：1.一般不会有错误，即使有错误,这个错误级别的SQL也不会走到这儿 2.并没有显示开启事务,但你这儿有rollback.3.万一出错，这儿return了，但游标还没销毁
                    --    BEGIN 
                    --        ROLLBACK TRAN
                    --        RETURN
                    --    END
                END
            CLOSE t_cursor
            DEALLOCATE t_cursor
        END
    ELSE 
        BEGIN
			PRINT '这儿缺少逻辑,创建存储过程时会报错的'     --这儿缺少逻辑,创建存储过程时会报错的
        END

Andy__Huang 2013-08-20

打赏
举报

在返回前可以用select 1或select 0或select '执行成功'或select '执行失败' 你是知道执行结果什么样了

...........
fetch next from t_cursor into @name 
if @@error!=0
begin 
select 0  -->0表示执行失败
rollback tran
return
end
end
select 1
close t_cursor
deallocate t_cursor
END
ELSE
BEGIN
select 1
END