Thank you!!

You mean to say that it never throws error back to application code(where try ,catch is used) if there is no proper error handling mechanism implemented in SQL.

But when i execute the outermost i.e SP1 which inturn executes SP2 i can see the error message in messages tab on sql server, though the SP1 is keep on getting executed after the error.

I never really get this why it is not throwing error to application code in my case (.net,java anythin)