Error Handling in Stored Procedure

[VIJSQL01]

hi,

I need to handle the error message in stored procedure.

I am doing following things...

1. First i will be loading data into a table from flat file which is of .CSV format.
2. I am loading the data along with header.
3. I use normal delete statement to delete the row which has header by comparing with column headers from the table.
The statement goes like this......

delete from dbo.ListingTrend_State_BE
where STATE_CODE='STATE_CODE' and STATE_NAME='STATE_NAME' and YYYYMM='YYYYMM' and DATA_PERIOD='DATA_PERIOD'

If all the columns match each other, the message should be displayed as header's are deleted. If any of the header is not matched, it should not delete the header, instead it should throw an error message saying this particular table header is not deleted.
Currently i am using following syntax. The execution of the stored procedure is happening successfully and a message 'header is deleted' is also displayed. To cross verify, we gave wrong header name in the file, but it is not throwing any error message for that particular table.

Begin

if object_id ('dbo.ListingTrend_State_BE') is not NULL
begin
delete from dbo.ListingTrend_State_BE
where STATE_CODE='STATE_CODE' and STATE_NAME='STATE_NAME' and YYYYMM='YYYYMM' and DATA_PERIOD='DATA_PERIOD'
and ALL_INV_COUNT='ALL_INV_COUNT' 
and ASK_SPRD_STD='ASK_SPRD_STD'
and MNT_SPLY_COUNT='MNT_SPLY_COUNT' 
and MKT_VELC='MKT_VELC' 
and MKT_INDC_MEAN='MKT_INDC_MEAN'
and MKT_INDC_MED='MKT_INDC_MED' 
and MKT_INDC_STD='MKT_INDC_STD'
select 'state header deleted'

end
else select 'state header not deleted'

Your help is required to trouble shoot this error.

Thanks in advance
VIJ

 

[gmmastros]

You could check the row count to determine if the header row is deleted.

if object_id ('dbo.ListingTrend_State_BE') is not NULL
  begin
    delete from dbo.ListingTrend_State_BE
    where STATE_CODE='STATE_CODE' and STATE_NAME='STATE_NAME' 
          and YYYYMM='YYYYMM' and DATA_PERIOD='DATA_PERIOD'
          and ALL_INV_COUNT='ALL_INV_COUNT' 
          and ASK_SPRD_STD='ASK_SPRD_STD'
          and MNT_SPLY_COUNT='MNT_SPLY_COUNT' 
          and MKT_VELC='MKT_VELC' 
          and MKT_INDC_MEAN='MKT_INDC_MEAN'
          and MKT_INDC_MED='MKT_INDC_MED' 
          and MKT_INDC_STD='MKT_INDC_STD'

    If @@RowCount = 0
      Begin
        Select 'header row not found'
      End
    Else
      Begin
        select 'state header deleted'
      End
  end
else 
  select 'state header not deleted'

You need to be careful when using @@RowCount. This internal variable is reset often. If you use it the way I show above, there shouldn't be any problems. If you want to use it later in the code, it's a good idea to set a local variable to this value.

For example:

Select 1 Where 'a'='b'
Select @@RowCount
Select @@RowCount

The first select statement doesn't return any rows, so @@RowCount returns 0. The second @@RowCount returns 1 because the first @@RowCount returned a value.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

hi,

This is awesome. I have implemented the same. It served my purpose. I would like to understand this implementation completely since i understood this partially now.

 If @@RowCount = 0      
Begin        
        Select 'header row not found'      
End   
    Else
      Begin
        select 'state header deleted' 
     End  
end
     else   
        select 'state header not deleted'

The first part:
If @@RowCount = 0
Begin
Select 'header row not found'

My understanding: I would like to understand the function of the variable @@Row Count at this place. I understand that, when we do delete operation, when delete happens for all the conditions(i mean for all columns), the row count would be treated as 1 else it will be treated as 0.
When Rowcount = 0, it mean that the deletion is not successful since all the conditions are not met and message would be displayed as 'Header Has An Issue'(i changed like this for my SP).

When Rowcount = 1, it mean that it is taking the count of the row which it is able to delete because all the conditions are met.In this case, it will go to the next statement in the loop 'Header is Deleted'

But i couldnt able to understand the last message, 'Header is not deleted' as it is not displayed for the table where header is not deleted. When exactly we can display this message? Could you please help me with this. I am just curious to know. It would be great, for the error headers if we display both messages 'Header Has AN Issue' and 'Header is not Deleted' at the same time. SO that user can understand header has an issue and it is not deleted from the table.

Thanks for your patience in helping me to understand this.

 

[ousoonerjoe]

Don't forget your TRY/CATCH blocks, nor RAISERROR. RAISERROR allows you to set, format and return an actual error message and error state:

RAISERROR(60000, 16, 1, 'Type your specific error message here.');

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[VIJSQL01]

Thanks very much. I handled this too.