Bubble up error in a stored procedure

I am working on creating a SQL Server 2005 stored procedure that will call another stored proc, which in turn could call another stored proc. If an error occurs in the grandchild proc, is it possible to have the error bubble up 开发者_开发技巧to the parent?

Yes, you can do this with the BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH construct in SQL Server.