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