[Original] Stored Procedure Application Problems and Solutions (paging code for example) (whether it is a Microsoft Bug)

This article only represents the understanding of the author at a certain stage.

I. Prerequisites for writing

Recently, a project uses LINQ to SQL, which provides applications for table, stored procedure, and function. For stored procedure, if your stored procedure can return a data set, LINQ to SQL automatically generates a corresponding strong result set object, and then easily reference it in our project. Two days ago, I encountered a particularly strange problem in my project, that is, I used a temp table in my stored procedure and then used the SELECT statement to select the data in the temp table, this stored procedure is not a strongly typed result set, but an int type. Let's analyze this problem, I also propose an inappropriate solution and seek a better solution.

II.Stored ProcedureInLINQ to SQLResult Analysis in 

Let's first analyze the problematic stored procude, as shown in table 1:

Chart 1: stored procedure1 Definition

-- ========================================================== ==== -- Author: Xiong Wei -- Create Date: 22 May 2010 -- Description: Search Group manage -- ========================================================== ===== Create Procedure [ DBO ] . [ Sp_test_one ] ( @ P_sort_exprission Nvarchar ( 50 ), @ P_paging_number Int , @ P_paging_size Int , @ P_total_count Int Output ) As Begin Select S. schoolname, C. * , Row_number () Over ( Order By C. OID ASC ) As Row_index Into # Temp From T_course As C Inner Join T_school As S On C. schooloid = S. OID Set @ P_total_count = @ Rowcount Select * From # Temp Where Row_index > = @ P_paging_number * @ P_paging_size + 1 And Row_index < @ P_paging_number * @ P_paging_size + 1 + @ P_paging_size Drop Table # Temp End Return

 

Can pass the aboveCode, We can see that the final result of it should be a set of records that pass through the distribution hand in the temp table. OK. Then we put the stored procedure in the LINQ to SQL. Let's see what result set he returned, as shown in table 2.

Chart 2: stored procedure1 returned results

Isn't it strange, because it returns an int type, not a data set.

Next let's look at another stored procedure, as shown in table 3.

Figure 3: stored procedure2 Definition

-- ========================================================== ===== -- Author: Xiong Wei -- Create Date: 22 May 2010 -- Description: Search Group manage -- ========================================================== ===== Create Procedure [ DBO ] . [ Sp_test_two ] ( @ P_sort_exprission Nvarchar ( 50 ), @ P_paging_number Int , @ P_paging_size Int , @ P_total_count Int Output ) As Begin Select S. schoolname, C. * , Row_number () Over ( Order By C. OID ASC ) As Row_index From T_course As C Inner Join T_school As S On C. schooloid = S. OID Set @ P_total_count = @ Rowcount End Return

First, let's not talk about the type returned by stored procedure defined in Chart 3. It is different from Chart 1 in terms of function. The returned result type is the same, but let's see if it returns an int type in LINQ to SQL? OK. Let's take a look at the results in Figure 4.

Figure 4: returned results of stored procedure2

In stored procedure2, LINQ to SQL generates a strong result set sp_test_tworesult. However, in the selection result, stored procedure1 and stored procedure2 return the same type of results, however, they are of different types in LINQ to SQL.

Iii. Solution to the Problem 

I checked the solution to this problem online and there was no direct solution. Then I analyzed that the two stored procedure results in the database should be of the same type, so I wonder if I can use stored procedure2 to generate the required strong type, then I used the code in stored procedure1 to replace the code in stored procedure2. So I tried it and found that it was okay, this proves that the execution results of stored procedure1 and stored procedure2 are the same, but different types are generated in LINQ to SQL. I don't know if this is a bug in Microsoft's LINQ to SQL. Please give me some advice.

Iv. Summary 

Although the above Code can solve my existing problems in the project, I believe this is not the best solution and there are many problems. For example, if we add a field to the output result set, we need to regenerate this strong type. Then we need to re-create a stored procedure for this result set, so this is not desirable. If you are free, I will study it again. I also hope you can give me some advice. I would like to express my gratitude here.