CREATE PROCEDURE [dbo].[SP_SelectProcessID]
@ProcessID INT,
@Message nvarchar(max) OUTPUT
AS
create table #TmpProcessID (id int identity(1,1), tmpStr nvarchar(50));
declare @n int
declare @rows int

truncate table #TmpProcessID
insert into #TmpProcessID
select distinct Tproject.project_name from TFlowDetails inner join Tproject on TFlowDetails.fd_project_id=Tproject.project_id
where TFlowDetails.fd_process_id =@ProcessID
select @rows=@@rowcount
declare @projectName nvarchar(50)
declare @WarMessage nvarchar(1000)
select @WarMessage=‘‘
select @n=1
while @n<=@rows
begin
select @projectName=tmpStr from #TmpProcessID where id=@n
select @WarMessage=@WarMessage+ @projectName+‘、‘
select @n= @n+1
end
if isnull(@WarMessage,‘‘)<>‘‘
begin
select @WarMessage=left(@WarMessage,len(@WarMessage)-1)
select @WarMessage=‘项目‘+@WarMessage+‘的流程图正在使用此工序，请在流程图中删除此工序后再删除！\r\n‘
end

truncate table #TmpProcessID
insert into #TmpProcessID
select distinct Tproject.project_name from TBudget inner join Tproject on TBudget.Project_ID=Tproject.project_id
where TBudget.Gx_ID=@ProcessID
select @rows=@@rowcount
declare @projectName1 nvarchar(50)
declare @WarMessage1 nvarchar(1000)
select @WarMessage1=‘‘
select @n=1
while @n<=@rows
begin
select @projectName1=tmpStr from #TmpProcessID where id=@n
select @WarMessage1=@WarMessage1+ @projectName1+‘、‘
select @n= @n+1
end
if isnull(@WarMessage1,‘‘)<>‘‘
begin
select @WarMessage1=left(@WarMessage1,len(@WarMessage1)-1)
select @WarMessage1=‘项目‘+@WarMessage1+‘对此工序进行了项目匡算，请删除此工序的项目匡算后再删除此工序！\r\n‘
end

truncate table #TmpProcessID
insert into #TmpProcessID
select distinct Tproject.project_name from Tflow_jump inner join Tproject on Tflow_jump.ProjectID=Tproject.project_id
where Tflow_jump.ProcessID=@ProcessID
select @rows=@@rowcount
declare @projectName2 nvarchar(50)
declare @WarMessage2 nvarchar(1000)
select @WarMessage2=‘‘
select @n=1
while @n<=@rows
begin
select @projectName2=tmpStr from #TmpProcessID where id=@n
select @WarMessage2=@WarMessage2+ @projectName2+‘、‘
select @n= @n+1
end
if isnull(@WarMessage2,‘‘)<>‘‘
begin
select @WarMessage2=left(@WarMessage2,len(@WarMessage2)-1)
select @WarMessage2=‘项目‘+@WarMessage2+‘对此工序进行了流程跳转配置，请先删除此工序的流程跳转后再删除此工序！\r\n‘
end

truncate table #TmpProcessID
insert into #TmpProcessID
select Tproject.project_name from TFlowCreateTask inner join Tproject on TFlowCreateTask.ProjectID=Tproject.project_id
where TFlowCreateTask.CurrentProcessID =@ProcessID
select @rows=@@rowcount
declare @projectName3 nvarchar(50)
declare @WarMessage3 nvarchar(1000)
select @WarMessage3=‘‘
select @n=1
while @n<=@rows
begin
select @projectName3=tmpStr from #TmpProcessID where id=@n
select @WarMessage3=@WarMessage3+ @projectName3+‘、‘
select @n= @n+1
end
if isnull(@WarMessage3,‘‘)<>‘‘
begin
select @WarMessage3=left(@WarMessage3,len(@WarMessage3)-1)
select @WarMessage3=‘项目‘+@WarMessage3+‘在任务直接生成中用到此工序，请先删除此工序直接生成的任务后再删除此工序！\r\n‘
end

truncate table #TmpProcessID
insert into #TmpProcessID
select distinct Tproject.project_name from WorkloadTemple inner join Tproject on WorkloadTemple.Projectid=Tproject.project_id
where WorkloadTemple.Processid =@ProcessID
select @rows=@@rowcount
declare @projectName4 nvarchar(50)
declare @WarMessage4 nvarchar(1000)
select @WarMessage4=‘‘
select @n=1
while @n<=@rows
begin
select @projectName4=tmpStr from #TmpProcessID where id=@n
select @WarMessage4=@WarMessage4+ @projectName4+‘、‘
select @n= @n+1
end
if isnull(@WarMessage4,‘‘)<>‘‘
begin
select @WarMessage4=left(@WarMessage4,len(@WarMessage4)-1)
select @WarMessage4=‘项目‘+@WarMessage4+‘的工作报告模板用到此工序，请先删除此工序的工作报告模板后再删除此工序！\r\n‘
end

truncate table #TmpProcessID
insert into #TmpProcessID
select distinct MIS_Users.uname from TUserSkill inner join MIS_Users on TUserSkill.UserId=MIS_Users.ID
where TUserSkill.ProcessId=@ProcessID
select @rows=@@rowcount
declare @projectName5 nvarchar(50)
declare @WarMessage5 nvarchar(1000)
select @WarMessage5=‘‘
select @n=1
while @n<=@rows
begin
select @projectName5=tmpStr from #TmpProcessID where id=@n
select @WarMessage5=@WarMessage5+ @projectName5+‘、‘
select @n= @n+1
end
if isnull(@WarMessage5,‘‘)<>‘‘
begin
select @WarMessage5=left(@WarMessage5,len(@WarMessage5)-1)
select @WarMessage5=‘用户‘+@WarMessage5+‘对此工序进行了技能设置，请删除此工序的技能设置后再删除此工序！\r\n‘
end

declare @num int
select @num= count( ProcessID) from Tgongxusoft where ProcessID =@ProcessID
if @num>0
begin
declare @WarMessage6 nvarchar(100)
select @WarMessage6=‘此工序配置有工艺程序软件，请删除此工序的工艺程序软件后再删除此工序！\r\n‘
select @Message=isnull(@WarMessage,‘‘)+isnull(@WarMessage1,‘‘)+isnull(@WarMessage2,‘‘)+isnull(@WarMessage3,‘‘)+isnull(@WarMessage4,‘‘)+isnull(@WarMessage5,‘‘)+isnull(@WarMessage6,‘‘)
end
else
begin
select @Message=isnull(@WarMessage,‘‘)+isnull(@WarMessage1,‘‘)+isnull(@WarMessage2,‘‘)+isnull(@WarMessage3,‘‘)+isnull(@WarMessage4,‘‘)+isnull(@WarMessage5,‘‘)
end

数据库临时表的用法