I didn’t mean to draw attention to this topic, nor do I want to give a textbook discussion of stored procedures in this article. A recent problem with stored procedures in a project reminded me of a colleague who asked me during a business trip to Wuhan last year:
I think stored procedures are good to use. Why don’t you recommend using them?
At that time, I seemed to have a thousand words in my mind, but I didn’t answer my colleagues with a concrete example after all. I just made a big comment on the conclusion. Compared with SQL, the code is more reusable, expanded and universal. Presumably my colleague wasn’t convinced.
Now that I think about it, the question I recently ran into is an example of how I can answer 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, naturally, requires reuse of 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 these three existing stored procedures in my C# code, things would have ended 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 whether a given user \ _ \ _ have a Job, Certification, and 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 network performance, from the interface design point of view, the incoming and returned values of the interface should be the data you need, should not have a large number of data that does not need or need the caller to preprocess. The purpose of the call is known from the semantic expression of the interface, and the code is much more readable.
Then change it. But unexpectedly, the problem came.
To illustrate the problem, I’ll simplify the code and assume that the system’s existing stored procedures look like this:
`CREATE PROCEDURE [dbo].[GetJobs]`
`(`
`@PersonId int,`
`@OrganizaitionId int`
`)`
`AS`
`BEGIN`
`SELECT JobId,JobName,JobType FROM Job WHERE PersonId = @PersonId AND OrganizaitionId = @OrganizaitionId`
`END`
I call it in the new stored procedure, and I need to get the number of jobs for that Person, which is the count for the result set that getJobs returns.
To do this, we first want to use a temporary table, where we store the returned result set into a temporary table, and then count count(*) on it:
`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`
This approach is simple and effective, but it has serious maintenance problems. If the returned result set fields of the called stored procedure change in the future, the temporary table structure in MyProc will need to change as well. This is unacceptable.
How about replacing INSERT INTO with SELECT INTO in MyProc? Unfortunately, the answer is no. SQL itself does not support this usage.
Output parameter to existing stored procedure getJobs? In this case, because GetJobs has already been called by multiple other code or SQL scripts, it would be risky to make changes to an existing stored procedure.
As I scour the web, an article by one of the masters of MS MVP summarizes almost any method for passing Data between Stored Procedures: How to Share Data between Stored Procedures. “He lamented in the article
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 failed to find a satisfactory solution, so I had no choice but to write the query for Jobs once in the newly written stored procedure.
Stored procedures have their advantages in many scenarios, such as performance. But for the general method of business logic, it is not recommended to write it in the stored procedure, code reuse, extension and client language, far from. It may eventually work, but the costs and risks are higher than the client-side language.
God knows if I’ll have another chance to discuss it with my former colleague.
