Raiserror Proc if table is empty

84 Views Asked by At

can anyone help me with raiserror in stored procedure. My Proc

Alter Proc Proc_Test
as

begin
declare @runID int;
set @runID = 
       ( 
       select max(isnull([RunID],0)) + 1
from [dbo].[Stored_Proc_Test]);
insert into [dbo].[Stored_Proc_Test]
 ( RunID,
   ServerName,
   DatabaseName,
   ExecutionUser,
   ProcName,
   StartDate)
select @runID,
       @@SERVERNAME,
       DB_NAME(),
       SYSTEM_USER,
       OBJECT_NAME(@@procid) as ProcName,
       GETDATE () as StartDate;

declare @Countrow int
set @Countrow= (select count(*) from [dbo].[Athletes] )

begin try
truncate table [dbo].[Athletes]

insert into [dbo].[Athletes]
select * from [dbo].[Athletes_Stored]

delete from [dbo].[Athletes_History]
where 1 = 1

insert into [dbo].[Athletes_History]
select * from [dbo].[Athletes]

update [dbo].[Stored_Proc_Test]
set EndDate  = getdate (),
    RowsAffectec = @@ROWCOUNT,
    TaskStatus = 'Complete successfully'
where RunID = @runID

end try
begin catch
update [dbo].[Stored_Proc_Test]
set Runcomments = 'Error on line' + Cast(Error_Line() as varchar(10)),
    TaskStatus = 'Failed',
    ErrorMsg = Cast(Error_Number() as varchar(20)) + ': '+ ERROR_MESSAGE()
    where RunID = @runID
end catch;
end

what I'm trying to do is if table [dbo].[Athletes] is EMPTY stop running the SP and throw message "EMPTY SOURCE". If table is not empty keep running the SP.

Thank you

0

There are 0 best solutions below