sqlserver 存储过程使用

创建存储过程

--创建存储过程
create procedure WMSendObj(
    --定义参数
    @org varchar(40),
    @dept varchar(40))
as
begin
    --定义游标
    declare cu cursor for select cno
                          from hr_arc_dispatch_obj
                          where cno in (select cno
                                        from hr_arc_dispatch_obj
                                        where INCLUDEDOWN = 1
                                          and COBJ = @org)
                            and cno not in (select cno
                                            from hr_arc_dispatch_obj
                                            where ctype = '3'
                                              and cobj = @dept)
    --定义变量
    declare @s_cno varchar(40)
    declare @i integer
    --开启游标
    OPEN cu;
    select @i = count(1) + 1 from hr_arc_dispatch_obj;
    --读取游标值
    fetch next from cu into @s_cno
    --循环
    while(@@fetch_status = 0)
        begin
            insert into hr_arc_dispatch_obj
            values (@i, '1', @s_cno, @dept, '3', '1', '0', null, null, null, null, null, null, 1, 1, 1,
                    '2021-12-01 15:14:37',
                    '2021-12-01 15:14:37',
                    1, 2);
            set @i = @i + 1
            fetch next from cu into @s_cno
        end
    --关闭游标
    close cu
    --释放游标
    deallocate cu
END
go

调用存储过程

declare @org  varchar(40) ='123'
declare @dept varchar(40) ='123'
exec WMSendObj @org, @dept