Is having a stored procedure that calls other stored procedures bad?

Question

I'm Trying to make a long stored procedure a little more manageable, Is it wrong to have a stored procedures that calls other stored procedures for example I want to have a sproc that inserts data into a table and depending on the type insert additional information into table for that type, something like:

BEGIN TRANSACTION

        INSERT INTO dbo.ITSUsage (
            Customer_ID,
            [Type],
            Source
        ) VALUES ( 
            @Customer_ID,
            @Type,
            @Source
            )
    SET @ID = SCOPE_IDENTITY()  

    IF @Type = 1
        BEGIN
                  exec usp_Type1_INS @ID, @UsageInfo 
            END
        IF @TYPE = 2
                BEGIN
                  exec usp_Type2_INS @ID, @UsageInfo 
            END

    IF (@@ERROR <> 0)
        ROLLBACK TRANSACTION
    ELSE
        COMMIT TRANSACTION      

Or is this something I should be handling in my application?

Answer 1

We call procs from other procs all the time. It's hard/impossible to segment a database-intensive (or database-only) application otherwise.

Answer 2

Calling a procedure from inside another procedure is perfectly acceptable.

However, in Transact-SQL relying on @@ERROR is prone to failure. Case in point, your code. It will fail to detect an insert failure, as well as any error produced inside the called procedures. This is because @@ERROR is reset with each statement executed and only retains the result of the very last statement. I have a blog entry that shows a correct template of error handling in Transact-SQL and transaction nesting. Also Erland Sommarskog has an article that is, for long time now, the reference read on error handling in Transact-SQL.