Automation Anywhere SQL results

Question

I am trying to capture if my SQL Query have 0 rows or multiple rows. If it has 0 rows then I will insert, if 1 will perform an update, if > 1 will perform additional analysis.

Is there a way I can see if my query resulted in x results or no results in automation anywhere?

Any assistance will be appreciated.

Answer 1

You can make use of if exists and if not exists and check if rows exists or not, or even if there are multiple before doing the insert.

Here is a simple example using if not exists where if the row doesn't exist on dbo.Table it will insert a row. If it already exists then the ID will be logged to an Error table.

declare @InsertID int = 5, @Name nvarchar(max) = 'some name'
if ((select count(1) from dbo.Table where ID = @InsertID) > 1) -- detect error; more than one record for an id
begin
    insert into dbo.Error (ErrorID, ErrorDate)
    select @InsertID, getdate()
end
else if not exists (select 1 from dbo.Table where ID = @InsertID) -- no record exists for ID, insert it
begin
    insert into dbo.Table (ID, Name)
    select @InsertID, @Name
else if exists (select 1 from dbo.Table where ID = @InsertID)  -- update the single record
begin
    update dbo.Table set Name = @Name where ID = @InsertID 
end