CODEWITHSUNDEEP
sqlsql-server
Bob The Janitor
Bob The Janitor

Reputation: 20802

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

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?

Upvotes: 6

Views: 7256

Answers (13)

eddo
eddo

Reputation: 2124

Adding to the correct comments of other posters, there is nothing wrong in principle but you need to watch out on the execution time in case the procedure is being called for instance by an external application which is conforming to a specific timeout.

Typical example if you call the stored procedure from a web application: when the default timeout kicks in since your chain of executions takes longer you get a failure in the web application even when the stored procedure committs correctly. Same happens if you call from an external service. This can lead to an inconsistent behaviour in your application, triggering error management routines in external services etc.

If you are in situations like this what I do is breaking the chain of calls redirecting the long execution children calls to different processes using a Service Broker.

Upvotes: 0

DBAndrew
DBAndrew

Reputation: 6958

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

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 10

nagul
nagul

Reputation: 2263

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.

Upvotes: 2

topchef
topchef

Reputation: 19803

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.

Upvotes: 1

Hardryv
Hardryv

Reputation: 793

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.

Upvotes: 1

Arnkrishn
Arnkrishn

Reputation: 30442

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

Upvotes: 3

Jason Irwin
Jason Irwin

Reputation: 2045

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

Upvotes: 2

Nicolas Simonet
Nicolas Simonet

Reputation: 533

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.

Upvotes: 4

BlackTigerX
BlackTigerX

Reputation: 6146

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

Upvotes: 3

Juri
Juri

Reputation: 32920

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.

Upvotes: 3

i_am_jorf
i_am_jorf

Reputation: 54620

No, it is perfectly acceptable.

Upvotes: 4

Joe
Joe

Reputation: 42656

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

Upvotes: 13

Related Questions