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

No, it is perfectly acceptable.

Answer 3

As long as it is within the same DB schema it is perfectly acceptable in my opinion. It is reuse which is always favorable to duplication. It's like calling methods within some application layer.

Answer 4

not at all, I would even say, it's recommended for the same reasons that you create methods in your code

Answer 5

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.

Answer 6

Definitely, no.

I've seen ginormous stored procedures doing 20 different things that would have really benefited from being refactored into smaller, single purposed ones.

Answer 7

No. It promotes reuse and allows for functionality to be componentized.

Answer 8

Hi

One stored procedure calling another stored procedure is fine. Just that there is a limit on the level of nesting till which you can go.

In SQL Server the current nesting level is returned by the @@NESTLEVEL function.

Please check the Stored Procedure Nesting section here http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx

cheers

Answer 9

In our IT area we use stored procedures to consolidate common code for both stored procedures and triggers (where applicable). It's also virtually mandatory for avoiding SQL source duplication.

Answer 10

The general answer to this question is, of course, No - it's normal and even preferred way of coding SQL stored procedures.

But it could be that in your specific case it is not such a good idea.

If you maintain a set of stored procedures that support data access tier (DAO) in your application (Java, .Net, etc.) then having database tier (let's call stored procedures that way) streamlined and relatively thin would benefit your overall design. Thus, having extensive graph of stored procedure calls may indeed be bad for maintaining and supporting overall data access logic in such application.

I would lean toward more uniform distribution of logic between DAO and database tier so that stored procedure code would fit inside single functional call.

Answer 11

As others have pointed out, this is perfectly acceptable and necessary to avoid duplicating functionality.

However, in Transact-SQL watch out for transactions in nested stored procedure calls: You need to check @@TRANCOUNT before issuing rollback transaction because it rolls back all nested transactions. Check this article for an in-depth explanation.

Answer 12

Yes it is bad. While SQL Server does support and allow one stored procedures to call another stored procedure. I would generally try to avoid this design if possible. My reason?

single responsibility principle