Does a stored procedure mistreat other stored procedures?

Question

I am trying to make a long stored procedure a little more manageable, is it wrong to have stored procedures that call other stored procedures, for example, I want to have sproc, which inserts data into the table and depending on the type, insert additional information into the table for this type, for example:

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 what I should handle in my application?

Answer 1

We always call procs from other processes. Otherwise, it is difficult or impossible to segment the database-oriented application (or only the database).

Answer 2

A procedure call from another procedure is perfectly acceptable.

However, Transact-SQL relying on @@ ERROR may be denied. The point is your code. It will not be able to detect insert failure, as well as any error that occurs inside the called procedures. This is due to the fact that @@ ERROR reset with each executed statement and saves the result of only the most recent statement. I have a blog post that has the correct error handling pattern in Transact-SQL and transaction nesting. Erland Sommarskog also has an article that for a long time refers to a link to error handling in Transact-SQL .

Answer 3

No, that’s perfectly acceptable.

Answer 4

Definitely not.

I saw ginormous stored procedures doing 20 different things that could really be reorganized into smaller, single-purpose ones.

Answer 5

As long as it is within the framework of one database schema, this is quite acceptable, in my opinion. This is reuse that always favors duplication. This is similar to calling methods within a certain application tier.

Answer 6

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

Answer 7

One stored procedure that calls another stored procedure is beautiful. It’s just that there is a limit at the nesting level to which you can go.

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

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

amuses

Answer 8

No. This promotes reuse and allows the functionality to be component.

Answer 9

As others have noted, this is perfectly acceptable and necessary to avoid duplication of functions.

However, in Transact-SQL, monitor transactions in nested calls of a stored procedure: you need to check @@TRANCOUNT before releasing rollback transaction , because it rolls back all nested transactions. Check out this article for a detailed explanation.

Answer 10

In our IT field, we use stored procedures to consolidate common code for both stored procedures and triggers (where applicable). It is also practically required to prevent duplication of the SQL source.

Answer 11

The general answer to this question is, of course, No - this is a normal and even preferred way to code SQL stored procedures.

But it may be that in your particular case this is not such a good idea.

If you support a set of stored procedures that support the data access level (DAO) in your application (Java, .Net, etc.), then the database ordering level (albeit the so-called stored procedures) is the overall design. Thus, having an extensive schedule of stored procedure calls can really be bad for maintaining and supporting the general data access logic in such an application.

I would be inclined towards a more even distribution of logic between the DAO and the database level, so that the stored procedure code is included in one functional call.

Answer 12

Yes this is bad. Although SQL Server supports and allows one stored procedure to call another stored procedure. As a rule, I tried to avoid this design. My mind?

principle of shared responsibility

Answer 13

Adding to the correct comments of other posters, in principle, there is nothing wrong, but you need to monitor the execution time if the procedure is called, for example, by an external application, which, in accordance with a certain timeout .

A typical example is if you call a stored procedure from a web application: when the default timeout expires because your execution chain takes longer, you get a failure in the web application, even if the stored procedure commits correctly. The same thing happens if you call from an external service. This can lead to inconsistent behavior in your application, the launch of error management procedures in external services, etc.

If you are in situations like this, what I am doing is breaking the chain of calls that redirect child calls with long execution to different processes using Service Broker .