May 7, 2018 at 1:20 pm
Hello,
I have a job that runs over 100 stored procedures daily. Each stored procedure has input tables and output tables.
Just in case there is an issue and one of the stored procedures fails, I'd like to know what other stored procedures have a problem from the failure.
There is a table that lists out the procedure name, table name and indicator (input table or output
create table dbo.test_backup_20180507 (
ID int identity(1,1),
ProcName varchar(100),
TableName varchar(100),
TableType varchar(10))
insert into dbo.test_backup_20180507 values ('Proc 0','A','Input')
insert into dbo.test_backup_20180507 values ('Proc 0','A1','Output')
insert into dbo.test_backup_20180507 values ('Proc 0','A2','Output')
insert into dbo.test_backup_20180507 values ('Proc 1','A1','Input')
insert into dbo.test_backup_20180507 values ('Proc 1','B','Output') <<< this stored procedure failed and table B and C didn't get created correctly.
insert into dbo.test_backup_20180507 values ('Proc 1','C','Output')
insert into dbo.test_backup_20180507 values ('Proc 2','X','Input')
insert into dbo.test_backup_20180507 values ('Proc 2','X1','Output')
insert into dbo.test_backup_20180507 values ('Proc 2','X2','Output')
insert into dbo.test_backup_20180507 values ('Proc 3','C','Input')
insert into dbo.test_backup_20180507 values ('Proc 3','C1','Output')
insert into dbo.test_backup_20180507 values ('Proc 3','C2','Output')
insert into dbo.test_backup_20180507 values ('Proc 4','C2','Input')
insert into dbo.test_backup_20180507 values ('Proc 4','C3','Output')
insert into dbo.test_backup_20180507 values ('Proc 4','C4','Output')
Is there a way to find all the stored procedures that uses the output table from the failed stored procedure using a recursive query?
In this case Proc 1 failed, so that output table B and C didn't get created correctly. Output table C is then used in Proc 3 and the output table of Proc 3 (which is C2) is used in Proc 4.
So the output results from the recursive query should be.. when the failed procedure is Proc 1?
ID ProcName TableName TableType
----------------------------------------------------------------------
4 Proc 1 A1 Input
5 Proc 1 B Output <<< Proc 1 failed and table B and C didn't get created correctly.
6 Proc 1 C Output
10 Proc 3 C Input
11 Proc 3 C1 Output
12 Proc 3 C2 Output
13 Proc 4 C2 Input
14 Proc 4 C3 Output
15 Proc 4 C4 Output
This results will tell me that after fixing Proc 1, I need to rerun Proc 3 and Proc 4
Thank you for your help
May 7, 2018 at 1:36 pm
I don't think you need recursion from this. But from the table you posted you have no way of ordering the rows so you couldn't know that A1 is correct. You need a way to know the order of tables being updated for a given procName.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2018 at 3:44 pm
thanks for the reply. I ended up using a recursive stored procedure. See attached and let me know if you have any comments. Thanks again
May 9, 2018 at 6:58 am
Coconut - Tuesday, May 8, 2018 3:44 PMthanks for the reply. I ended up using a recursive stored procedure. See attached and let me know if you have any comments. Thanks again
There is nothing attached.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2018 at 5:35 pm
it looks like I can only post certain types of files. I posted the code instead. Sorry for the confusion.
Permitted file types: gif- bmp- jpe- jpeg- pdf- doc- rtf- png- txt- zip- xls- jpg- ascx- css- aspx- rar- docx- xlsx- sqlplan- rdl.
-- STEP 1. Create input
use EDW
go
/*
create table dbo.ProcTableList (
ID int identity(1,1),
ProcName varchar(100),
TableName varchar(100),
TableType varchar(10))
insert into dbo.ProcTableList values ('Proc 0','A','Input')
insert into dbo.ProcTableList values ('Proc 0','A1','Output')
insert into dbo.ProcTableList values ('Proc 0','A2','Output')
insert into dbo.ProcTableList values ('Proc 1','A1','Input')
insert into dbo.ProcTableList values ('Proc 1','B','Output')
insert into dbo.ProcTableList values ('Proc 1','C','Output')
insert into dbo.ProcTableList values ('Proc 2','X','Input')
insert into dbo.ProcTableList values ('Proc 2','X1','Output')
insert into dbo.ProcTableList values ('Proc 2','X2','Output')
insert into dbo.ProcTableList values ('Proc 3','C','Input')
insert into dbo.ProcTableList values ('Proc 3','C1','Output')
insert into dbo.ProcTableList values ('Proc 3','C2','Output')
insert into dbo.ProcTableList values ('Proc 4','C2','Input')
insert into dbo.ProcTableList values ('Proc 4','C3','Output')
insert into dbo.ProcTableList values ('Proc 4','C4','Output')
*/
if object_id('dbo.p_Update_ProcTableList','P') is not null
drop procedure dbo.p_Update_ProcTableList
go
create procedure dbo.p_Update_ProcTableList (@ProcName varchar(100), @InputTable varchar(100), @OutputTable varchar(100))
as
begin
declare @ID int, @sql nvarchar(max)
if object_id('dbo.ProcTableList_FNL') is null
begin
-- drop table dbo.ProcTableList_FNL
create table dbo.ProcTableList_FNL (
ID int identity(1,1),
ProcName varchar(100),
TableName varchar(100),
TableType varchar(10),
Processed char(1),
AuditInsertTimestamp datetime2(3) default getdate())
end
set @sql = 'Top of the lines'
raiserror(@SQL,0,1) with nowait
if (@ProcName is not null or @InputTable is not null or @OutputTable is not null)
begin
set @sql = 'Initial Insert started'
raiserror(@SQL,0,1) with nowait
insert into dbo.ProcTableList_FNL (
ProcName,
TableName,
TableType,
Processed)
select
a.ProcName,
a.TableName,
a.TableType,
case when a.TableType = 'Input' then 'Y' else 'N' end Processed
from dbo.ProcTableList a with (nolock)
where
a.ProcName = @ProcName or
a.TableName = @InputTable or
a.TableName = @OutputTable
set @sql = 'Initial Insert complete..' + cast(@@ROWCOUNT as varchar)
raiserror(@SQL,0,1) with nowait
exec dbo.p_Update_ProcTableList
@ProcName = null,
@InputTable = null,
@OutputTable = null
end
else
begin
set @sql = 'Recursive started'
raiserror(@SQL,0,1) with nowait
if (select count(*) from dbo.ProcTableList_FNL a with (nolock) where a.Processed = 'N') > 0
begin
select @ID = max(a.ID) from dbo.ProcTableList_FNL a with (nolock) where a.Processed = 'N'
set @sql = 'Processing.. ' + cast(@ID as varchar)
raiserror(@SQL,0,1) with nowait
insert into dbo.ProcTableList_FNL (
ProcName,
TableName,
TableType,
Processed)
select
from (
select
distinct
a.ProcName,
a.TableName,
a.TableType,
case when a.TableType = 'Input' then 'Y' else 'N' end Processed
from dbo.ProcTableList a with (nolock)
where
a.ProcName in (
select distinct a.ProcName
from dbo.ProcTableList a with (nolock)
where a.TableName in (
select
TableName
from dbo.ProcTableList_FNL a with (nolock)
where
a.ID = @ID) and a.TableType = 'Input')) a
left join dbo.ProcTableList_FNL b on a.ProcName = b.ProcName and a.TableName = b.TableName and a.TableType = b.TableType
where
b.ProcName is null
update a set a.Processed = 'Y'
from dbo.ProcTableList_FNL a
where
a.ID = @ID
exec dbo.p_Update_ProcTableList
@ProcName = null,
@InputTable = null,
@OutputTable = null
end
else
begin
select * from dbo.ProcTableList_FNL order by 1
if object_id('dbo.ProcTableList_FNL') is not null
drop table dbo.ProcTableList_FNL
return;
end
set @sql = 'Recursive completed'
raiserror(@SQL,0,1) with nowait
end
end
go
exec dbo.p_Update_ProcTableList
@ProcName = 'Proc 1',
@InputTable = null,
@OutputTable = null
/*
select * from dbo.ProcTableList order by 1
select * from dbo.ProcTableList_FNL order by 2
truncate table dbo.ProcTableList_FNL
*/
/*
ID ProcName TableName TableType
----------------------------------------------------------------------
4 Proc 1 A1 Input
5 Proc 1 B Output <<< Proc 1 failed and table B and C didn't get created correctly.
6 Proc 1 C Output
10 Proc 3 C Input
11 Proc 3 C1 Output
12 Proc 3 C2 Output
13 Proc 4 C2 Input
14 Proc 4 C3 Output
15 Proc 4 C4 Output
*/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply