Link to home
Create AccountLog in
Avatar of JGH5
JGH5Flag for United States of America

asked on

Try Catch Record Count Greater than Zero in a table

I am trying to make sure that dbo.sp_Test does not execute if there are no records in the dbo.Data_tbl

I can not use if @@rowcount > 0 since BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0

Not sure if i am on the right path with:  IF NOT EXISTS(select 'Table_Name' from dbo.Data_Tbl)

.....any suggestions?


///////////////////////////////////////

BEGIN TRY

IF NOT EXISTS(select 'Table_Name' from dbo.Data_Tbl)
   
BEGIN TRANSACTION      
     
      INSERT INTO dbo.Data_Tbl
            (Table_Name)
    VALUES ('Detail')

      WHILE (SELECT Row_Count_Differential
         FROM dbo.Data_Tbl_Raw
        WHERE Server_Alias = 'Test' and
              Table_Name ='Test_HX' AND
              Run_Date =CONVERT(VARCHAR,GETDATE(),112)) <>0
             
BEGIN EXEC dbo.sp_Test
 
                 
COMMIT              
 
END TRY  
     
BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

SELECT 'An error has occurred at line ' +
LTRIM(STR(ERROR_LINE())) +
' with error ' + LTRIM(STR(ERROR_NUMBER())) + ' ' + ERROR_MESSAGE()

END CATCH

/////////////////////////////////////////////////////
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of JGH5

ASKER

Thank you.  IF (select count(*) from data_tbl) > 0

works just fine.  I hit a thinking wall....
No problems, we all hit those walls from time to time :)