if exists(select * from sysobjects where name='proc_PubFileType')
drop proc proc_PubFileType
go
create proc proc_PubFileType
@TypeOID varchar(36),
@flag int output
as
begin tran
declare @OutFileOIDs varchar(8000),@InstanceIDs varchar(8000),@err int
set @err=0
set @OutFileOIDs=''
set @InstanceIDs=''
set @flag=0 --假设删除成功
--获得该类型下所有的公文信息(语句格式必须是'a','b')
select @OutFileOIDs=@OutFileOIDs+''''+cast(outfileoid as varchar(36))+''''+',' from outfile where pubfiletypeoid like '%'+@TypeOID+'%'
if(len(@OutFileOIDs)>0)
begin
set @OutFileOIDs=substring(@OutFileOIDs,1,len(@OutFileOIDs)-1)
--获得每个公文对应的InstanceID
--select instanceid from outfile where outfileoid in('0005B54B-0000-0000-0000-000020BE02A4','000DF9CC-0000-0000-0000-000020C621DD')
--去掉最两边的''
--set @OutFileOIDs=substring(@OutFileOIDs,2,len(@OutFileOIDs)-2)
/*declare @a varchar(100)
set @a='''0005B54B-0000-0000-0000-000020BE02A4'''+','+'''000DF9CC-0000-0000-0000-000020C621DD'''
print @a
必须用exec()
exec('select * from outfile where outfileoid in('+@a+')')*/
declare @a varchar(8000)
set @a=''
exec('declare @InstanceIDs varchar(8000) select @InstanceIDs=@InstanceIDs+cast(instanceid as varchar(36))+'','' from outfile where outfileoid in('+@OutFileOIDs+') set '+@a+'=''00''')//上面一句总是提示错误,给我帮看一下
--exec('select '+@InstanceIDs+'='+@InstanceIDs+'+cast(instanceid as varchar(36)) from outfile where outfileoid in('+@OutFileOIDs+')')
--select @InstanceIDs=@InstanceIDs+cast(instanceid as varchar(36))+',' from outfile where outfileoid in(@OutFileOIDs)
print '流程编号:'+@InstanceIDs
if(len(@InstanceIDs)>0)
begin
set @InstanceIDs=substring(@InstanceIDs,1,len(@InstanceIDs)-1)
--有流程信息了才可以删除流程信息
declare @TaskTicketOIDs varchar(8000)
set @TaskTicketOIDs=''
--获得所有的任务oid
select @TaskTicketOIDs=@TaskTicketOIDs+cast(TaskTicketOID as varchar(36))+',' from taskticket where instanceid in(@InstanceIDs)
print '任务编号:'+@TaskTicketOIDs
if(len(@TaskTicketOIDs)>0)
begin
set @TaskTicketOIDs=substring(@TaskTicketOIDs,1,len(@TaskTicketOIDs)-1)
--删除表TransConditionEx表中的记录
--delete from transconditionex where taskticket_fk in(@TaskTicketOIDs)
set @err=@err+@@error
end
else
begin
set @err=@err+@@error
end
--删除assigntask,taskresult,flowinstance,finststatedata中的信息
--删除表TaskResult表中的记录
print '执行到这应该执行了啊'
-- delete from taskresult where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表FInstStateData表中的记录
--delete from finststatedata where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表AssignTask表中的记录
--delete from assigntask where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表TaskTicket表中的记录
--delete from taskticket where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除FlowInstance表中的记录
--delete from flowinstance where flowinstanceoid in(@InstanceIDs)
set @err=@err+@@error
--删除outfile表中的记录
--delete from outfile where outfileoid in(@OutFileOIDs)
set @err=@err+@@error
--删除pubfiletype表中的记录
--delete from pubfiletype where pubfiletypeoid like '%'+@TypeOID+'%'
set @err=@err+@@error
end
else
begin
--没有流程信息,但有公文信息
--delete from outfile where outfileoid in(@OutFileOIDs)
set @err=@err+@@error
--删除该类型
--delete from pubfiletype where pubfiletypeoid=@TypeOID
set @err=@err+@@error
end
set @err=@err+@@error
end
else
begin
--该类型下没有公文信息和流程信息只需删除该类型即可
delete from pubfiletype where pubfiletypeoid like '%'+@TypeOID+'%'
set @err=@err+@@error
end
if(@err<>0)
begin
set @flag=1 --删除失败
rollback tran
end
else
begin
commit tran
end
go
drop proc proc_PubFileType
go
create proc proc_PubFileType
@TypeOID varchar(36),
@flag int output
as
begin tran
declare @OutFileOIDs varchar(8000),@InstanceIDs varchar(8000),@err int
set @err=0
set @OutFileOIDs=''
set @InstanceIDs=''
set @flag=0 --假设删除成功
--获得该类型下所有的公文信息(语句格式必须是'a','b')
select @OutFileOIDs=@OutFileOIDs+''''+cast(outfileoid as varchar(36))+''''+',' from outfile where pubfiletypeoid like '%'+@TypeOID+'%'
if(len(@OutFileOIDs)>0)
begin
set @OutFileOIDs=substring(@OutFileOIDs,1,len(@OutFileOIDs)-1)
--获得每个公文对应的InstanceID
--select instanceid from outfile where outfileoid in('0005B54B-0000-0000-0000-000020BE02A4','000DF9CC-0000-0000-0000-000020C621DD')
--去掉最两边的''
--set @OutFileOIDs=substring(@OutFileOIDs,2,len(@OutFileOIDs)-2)
/*declare @a varchar(100)
set @a='''0005B54B-0000-0000-0000-000020BE02A4'''+','+'''000DF9CC-0000-0000-0000-000020C621DD'''
print @a
必须用exec()
exec('select * from outfile where outfileoid in('+@a+')')*/
declare @a varchar(8000)
set @a=''
exec('declare @InstanceIDs varchar(8000) select @InstanceIDs=@InstanceIDs+cast(instanceid as varchar(36))+'','' from outfile where outfileoid in('+@OutFileOIDs+') set '+@a+'=''00''')//上面一句总是提示错误,给我帮看一下
--exec('select '+@InstanceIDs+'='+@InstanceIDs+'+cast(instanceid as varchar(36)) from outfile where outfileoid in('+@OutFileOIDs+')')
--select @InstanceIDs=@InstanceIDs+cast(instanceid as varchar(36))+',' from outfile where outfileoid in(@OutFileOIDs)
print '流程编号:'+@InstanceIDs
if(len(@InstanceIDs)>0)
begin
set @InstanceIDs=substring(@InstanceIDs,1,len(@InstanceIDs)-1)
--有流程信息了才可以删除流程信息
declare @TaskTicketOIDs varchar(8000)
set @TaskTicketOIDs=''
--获得所有的任务oid
select @TaskTicketOIDs=@TaskTicketOIDs+cast(TaskTicketOID as varchar(36))+',' from taskticket where instanceid in(@InstanceIDs)
print '任务编号:'+@TaskTicketOIDs
if(len(@TaskTicketOIDs)>0)
begin
set @TaskTicketOIDs=substring(@TaskTicketOIDs,1,len(@TaskTicketOIDs)-1)
--删除表TransConditionEx表中的记录
--delete from transconditionex where taskticket_fk in(@TaskTicketOIDs)
set @err=@err+@@error
end
else
begin
set @err=@err+@@error
end
--删除assigntask,taskresult,flowinstance,finststatedata中的信息
--删除表TaskResult表中的记录
print '执行到这应该执行了啊'
-- delete from taskresult where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表FInstStateData表中的记录
--delete from finststatedata where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表AssignTask表中的记录
--delete from assigntask where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除表TaskTicket表中的记录
--delete from taskticket where instanceid in(@InstanceIDs)
set @err=@err+@@error
--删除FlowInstance表中的记录
--delete from flowinstance where flowinstanceoid in(@InstanceIDs)
set @err=@err+@@error
--删除outfile表中的记录
--delete from outfile where outfileoid in(@OutFileOIDs)
set @err=@err+@@error
--删除pubfiletype表中的记录
--delete from pubfiletype where pubfiletypeoid like '%'+@TypeOID+'%'
set @err=@err+@@error
end
else
begin
--没有流程信息,但有公文信息
--delete from outfile where outfileoid in(@OutFileOIDs)
set @err=@err+@@error
--删除该类型
--delete from pubfiletype where pubfiletypeoid=@TypeOID
set @err=@err+@@error
end
set @err=@err+@@error
end
else
begin
--该类型下没有公文信息和流程信息只需删除该类型即可
delete from pubfiletype where pubfiletypeoid like '%'+@TypeOID+'%'
set @err=@err+@@error
end
if(@err<>0)
begin
set @flag=1 --删除失败
rollback tran
end
else
begin
commit tran
end
go
总是提示'='那儿有错误
set @a=''
exec('declare @InstanceIDs varchar(8000) select @InstanceIDs=@InstanceIDs+cast(instanceid as varchar(36))+'','' from outfile where outfileoid in('+@OutFileOIDs+') set '+@a+'=''00''')
错误地方时:set '+@a+'=''00'''
我想在exec()里面给一个已定义变量赋值,提示=附近语法错误
exec('select '+@InstanceIDs+'='+@InstanceIDs+'cast(instanceid as varchar(36))+'','' from outfile where outfileoid in('+@OutFileOIDs+')')
这里的@InstanceIDs是上面已经定义的