I’m not trying to draw attention to this topic, nor am I trying to write a textbook account of stored procedures in this article. The stored procedure problems encountered in recent projects remind me of a question asked by a colleague during a business trip to Wuhan last year:

I think stored procedures work fine. Why don’t you recommend them?

At that time, I seemed to have a lot of words, but I didn’t answer my colleagues with a concrete example. I just rambled on from the conclusion that the code was more reusable, extensible and versatile than SQL. Presumably colleagues are not convinced.

Now that I think about it, my recent question is an example of an answer to a colleague.

Recent projects have a new demand, need to check if a user has a Job, Certification, Disclosure the three business data.

Found through the code, the system of the user’s personal page c # code calls the three stored procedures, to grab the user’s Job, Certification, and Disclosure of data. My new requirement, of course, needs to reuse these three stored procedures, otherwise:

If each write a fetching data business logic code, if change the business logic, it is difficult to pursue and maintain all read the Job, Certification, Disclosure of SQL.

If I had called the three existing stored procedures in C# code, things would have been over pretty quickly. That’s what I did.

But the code reviewer suggests that I need, does not need the Job, Certification, and Disclosure of the three business object data. I just need to have the Job whether a given user _ _ Certification, Disclosure. So I should be whether the presence of the Job, Certification, Disclosure of judgment logic is written in the database, finally from the database to the web server through the network is the only true or false, the save the network traffic, it is best.

Also on. In addition to the network performance, from the perspective of interface design, the incoming and returned values of the interface should be the data you need, and there should not be a lot of data that does not need or need caller to be preprocessed. Knowing the purpose of the call from the semantics of the interface greatly improves the readability of the code.

Then do it. But what I didn’t expect was a problem.

To illustrate the problem, I simplify the code and assume that the system’s existing stored procedures are as follows:

`CREATE PROCEDURE [dbo].[GetJobs]`
`(`
 `@PersonId int,`
 `@OrganizaitionId int`
 `)`
`AS`
`BEGIN`
 `SELECT JobId,JobName,JobType FROM Job WHERE PersonId = @PersonId AND OrganizaitionId = @OrganizaitionId`
`END`

Copy the code

I call it in a new stored procedure, and I need to get the number of jobs for that person, which is the count of the result set that GetJobs returns.

To do this, the first thing to think about is using a temporary table, storing the returned result set into the temporary table, and counting it with count(*) :

`CREATE PROCEDURE [dbo].[MyProc]`
`(`
 `@PersonId int,`
 `@OrganizaitionId int,`
 `)`
`AS`
`BEGIN`
 `CREATE TABLE #Temp(`
 `PersonId int,`
 `OrganizaitionId int`
 `)`
 `INSERT INTO #Temp EXEC dbo.GetJobs`
 `@PersonId = @PersonId,`
 `@ParentOrgId = @ParentOrgId`
 `SELECT COUNT(*) FROM #Temp`
`END`

Copy the code

This approach is simple and effective, but it has serious maintenance problems. If the return result set field of the called stored procedure changes in the future, the temporary table structure in MyProc will need to change as well. This is unacceptable.

What about replacing INSERT INTO in MyProc with SELECT INTO? Unfortunately, the answer is no. SQL itself does not support this usage.

Add output to the existing stored procedure GetJobs? In this case, because GetJobs has been called by multiple other code or SQL scripts, it is risky to change an existing stored procedure.

I searched the Internet and found an article written by MS MVP summarized almost all the methods of transferring Data between Stored Procedures: How to Share Data between Stored Procedures. “He wrote with resignation

Keep in mind that compared to languages such as C# and Java, Transact-SQL is poorly equipped for code reuse, why solutions in T‑SQL to reuse code are clumsier.

In the end, I couldn’t find a satisfactory solution, so I wrote the query for Jobs in the newly written stored procedure.

Stored procedures have advantages in many scenarios, such as performance. However, for the general method of business logic, it is not recommended to write it in stored procedures, code reuse and extension compared to the client language, far from. It may eventually be possible, but the costs and risks are higher than those of the client language.

Who knows if there will be a chance to revisit the subject with that former colleague.