CREATE PROCEDURE [dbo]. [Sp_selectprocessid]
@ProcessID INT,
@Message nvarchar (max) OUTPUT
As
CREATE TABLE #TmpProcessID (ID int identity (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 [email protected]
Select @[email Protected] @rowcount
declare @projectName nvarchar (50)
declare @WarMessage nvarchar (1000)
Select @WarMessage = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName + ', '
Select @n= @n+1
End
If IsNull (@WarMessage, ') <> '
Begin
Select @WarMessage =left (@WarMessage, Len (@WarMessage)-1)
Select @WarMessage = ' project ' [email protected]+ ' Flowchart is using this operation, please delete this operation in the flowchart before deleting! \ 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 [email protected]
Select @[email Protected] @rowcount
declare @projectName1 nvarchar (50)
declare @WarMessage1 nvarchar (1000)
Select @WarMessage1 = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName1 =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName1 + ', '
Select @n= @n+1
End
If IsNull (@WarMessage1, ') <> '
Begin
Select @WarMessage1 =left (@WarMessage1, Len (@WarMessage1)-1)
Select @WarMessage1 = ' project ' [email protected]+ ' Project perspective calculations this operation, delete the project perspective calculations this operation before deleting this operation! \ r \ n '
End
TRUNCATE TABLE #TmpProcessID
INSERT INTO #TmpProcessID
Select distinct tproject.project_name from Tflow_jump inner joins Tproject on Tflow_jump. projectid=tproject.project_id
where [email protected]
Select @[email Protected] @rowcount
declare @projectName2 nvarchar (50)
declare @WarMessage2 nvarchar (1000)
Select @WarMessage2 = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName2 =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName2 + ', '
Select @n= @n+1
End
If IsNull (@WarMessage2, ') <> '
Begin
Select @WarMessage2 =left (@WarMessage2, Len (@WarMessage2)-1)
Select @WarMessage2 = ' project ' [email protected]+ ' To configure the process for this operation, please remove the process jump before deleting this operation! \ r \ n '
End
TRUNCATE TABLE #TmpProcessID
INSERT INTO #TmpProcessID
Select Tproject.project_name from Tflowcreatetask inner joins Tproject on tflowcreatetask.projectid=tproject.project_id
where Tflowcreatetask.currentprocessid [email protected]
Select @[email Protected] @rowcount
declare @projectName3 nvarchar (50)
declare @WarMessage3 nvarchar (1000)
Select @WarMessage3 = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName3 =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName3 + ', '
Select @n= @n+1
End
If IsNull (@WarMessage3, ') <> '
Begin
Select @WarMessage3 =left (@WarMessage3, Len (@WarMessage3)-1)
Select @WarMessage3 = ' project ' [email protected]+ ' This operation is used in direct generation of the task, please remove the task generated directly from this operation before deleting this operation! \ 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 [email protected]
Select @[email Protected] @rowcount
declare @projectName4 nvarchar (50)
declare @WarMessage4 nvarchar (1000)
Select @WarMessage4 = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName4 =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName4 + ', '
Select @n= @n+1
End
If IsNull (@WarMessage4, ') <> '
Begin
Select @WarMessage4 =left (@WarMessage4, Len (@WarMessage4)-1)
Select @WarMessage4 = ' project ' [email protected]+ ' Work report template is used for this operation, please delete the work report template for this operation before deleting this operation! \ 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 [email protected]
Select @[email Protected] @rowcount
declare @projectName5 nvarchar (50)
declare @WarMessage5 nvarchar (1000)
Select @WarMessage5 = "
Select @n=1
While @n<[email protected]
Begin
Select @projectName5 =tmpstr from #TmpProcessID where [email protected]
Select @[email protected]+ @projectName5 + ', '
Select @n= @n+1
End
If IsNull (@WarMessage5, ') <> '
Begin
Select @WarMessage5 =left (@WarMessage5, Len (@WarMessage5)-1)
Select @WarMessage5 = ' user ' [email protected]+ ' The skill set for this operation, remove the skill set for this operation before deleting this operation! \ r \ n '
End
DECLARE @num int
Select @num = count (ProcessID) from Tgongxusoft where ProcessID [email protected]
If @num >0
Begin
declare @WarMessage6 nvarchar (100)
Select @WarMessage6 = ' process software is configured for this operation, please remove the process software for this operation before deleting this operation! \ 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
Usage of the database temp table