Recursion in T-SQL

Recursion in the T-SQL

Author: Alexander Kozak
Technology: T-SQL
Difficulty:★★★☆☆
Target Audience: dBA and database developers

[Abstract]Recursion is one of the classic skills. students majoring in computer science often write tower pigProgramTo learn about it. In this articleArticleAlex Kozak discusses recursion in T-SQL.

I once worked as a university teacher. When I started teaching subqueries, I asked the students to find the youngest employee from the employees table in the northwind database. Most students give the following answers easily.

Select *
From employees
Where birthdate =
(Select max (birthdate) from employees)

However, when I asked them to find the next youngest employee, many people were stumped. Several students gave the following answers.

Select *
From employees
Where birthdate =
(Select max (birthdate)
From employees
Where birthdate <
(Select max (birthdate) from employees ))

The recursion feature of this issue is obvious. I remember writing a stored procedure for the nth record that can return any data level (age, weight, score, etc. However, it was not until two years that I finally completed the compilation of this stored procedure when I was working on an e-commerce project funded by someone.

Overview

Recursion occurs when a stored procedure or function calls itself. It is a widely known and useful mathematical and programming concept. However, it is also very dangerous, for example, it will lead to an infinite loop. (This may be one reason why sqlserver2000 limits the number of nested calls and the number of nested layers to 32. When you run a stored procedure, use the full-process variable @ nestlevell to check the nested layer of the process .) DBMS programmers should keep in mind that recursive calls can significantly prolong the transaction processing time. Therefore, recursion is usually avoided in online transaction processing systems.

Let's start with an example. Suppose you have a table named checkrank that contains the student test scores. Your task is to find out the students whose scores rank between 4th and 10th. I wrote a script named fillcheckrank and a stored procedure named spu_testrecursion to demonstrate this technique. This script creates and loads the checkrank table with the relevant random data (see createcheckrank. SQL on the attached disc), but the stored procedure (see list 1) is much more complex and uses recursion.Algorithm, Nested procedure call, and nested subquery to calculate the answer.

List 1 spu_testrecursion stored procedure:

If exists (select * From sysobjects
Where id = object_id ('spu _ testrecursion ')
And objectproperty (ID, 'isprocedure ') = 1)
Drop procedure spu_testrecursion
Go
Create proc spu_testrecursion
@ Level int, @ tblname varchar (30 ),
@ Colname varchar (30), @ answer varchar (8000) Output
As
Declare @ one_less int
Set nocount on
-- Parameter @ level greater than 31 is disallowed.
If (@ level <0 or @ level> 31)
Begin
Print 'illegal parameter value. Must be 0 through 31'
Return-1
End
If (@ level = 0 or @ level = 1)
Begin
Select @ answer = 'select max ('+ @ colname + ')
From '+ @ tblname
End
Else
Begin
Select @ one_less = @ level-1
-- Recursively call itself
Exec spu_testrecursion @ one_less, @ tblname,
@ Colname, @ answer output
If @ error <> 0 return (-1)
Select @ answer = 'select max ('+ @ colname + ')
From '+ @ tblname + 'where' + @ colname +
'<' + Char (10) + '(' + @ answer + ')'
End
If @ nestlevel = 1
Begin
Print 'nested level'
+ Cast (@ nestlevel as varchar (20) + char (10) +
@ Colname + 'rank '+ Cast (@ level as varchar (10 ))
+ Char (10) + @ answer
Exec (@ answer)
End
Return (0)
Go

/* How to run the procedure
Declare @ answer varchar (8000)
Exec spu_testrecursion 10, 'checkrank ',
'Testpoints', @ answer output
*/

Note: When you delete or create a stored procedure, you will receive information that the row cannot be added to sysdepend of the current stored procedure due to the missing "spu_testrecursion. But do not worry, this stored procedure can still be created. For more information, see q24641.

This stored procedure receives these parameters

• @ Level: the level or position in the level.
• @ Tblname: Table Name
• @ Answer: return the output parameters of the generated SELECT statement.

The two parameters are returned:

• Value, corresponding to the level or position required in the hierarchy
• A script that you can get the same result

To get the result with the fourth place, you can do this:

Declare @ answer varchar (4000)
Exec spu_testrecursion 4, 'checkrank ', 'testpoints ',
@ Answer output

The following is my results (your results may be different because the data in the table is randomly generated ).

Nested Level 1
Testpoints Rank 4
Select max (testpoints) from checkrank where testpoints <
(Select max (testpoints) from checkrank where testpoints <
(Select max (testpoints) from checkrank where testpoints <
(Select max (testpoints) from checkrank )))
-----------
93

In this way, the score corresponding to the fourth place is 93.

When I started to execute the same query to get a score of 10th, my answer was 87. The final answer to the 4th to 10th ranking questions can also be inferred by searching, or by running a query (see 4thru10th. SQL in the attached CD)

Example

The following scenarios are common for many e-commerce transactions. Assume that the buyer and the seller start the transaction, quotation or inquiry. The quotation and inquiry can be sent to a limited number of buyers (sellers) or to all members participating in the price exchange.

When the first bid or response to the inquiry arrives, the transaction starts. From this moment on, various situations are possible, and each scenario creates its own transaction chain. Quotation, bid, or other parts of the chain can be terminated, canceled, rejected, or accepted. Users can send a counter-offer and receive another counter-offer. This cycle can start repeatedly according to market rules. Deviations from basic rules are also allowed. For example, you may allow the parties to make limited changes to the transaction that has been completed, such as accepting or rejecting the transaction in sequence.

The actual execution of transactions may change in details, however, each part of a transaction chain is usually stored as an XML document, a Java object, or a document that can be split and stored in a table. You can use the document path to find the order of these documents in the transaction chain. This is similar to a chain table. Apart from the root and end files, components in each table (PATH) are associated with the preceding and subsequent files.

For example, assume that a table named documents contains all documents and a column named docpath. For rows with docid (primary key) = 12315 and docpath = 12217/12267/12299/12315, there is a negotiation chain: 12217 (as the original quoted materials of the root document or template ). 12267 (proposed quotation item-actual quotation ). 12299 (BID ). 12315 (reverse document)

Now let's assume that I want to analyze the transaction process and find the price, freight, quantity, and value differences between the Final Document and the original document. If I want to analyze the possibility of transaction failure, I must mark the document with the canceled, terminated, or rejected status. To analyze the actual value and quantity, I need to extract the protocol and purchase order in the receiving status. In both cases, the final document will be the last document in docpath (12315 in our example), but the original document is not the first document. In my example, the first document (12217) is a template with only basic quotation parameters. (Only when I get a bid can I calculate the goods fee, total price, and other parameters .) Therefore, in my example, the first 2nd documents (12267) are an original document. In short, any document from the transaction chain, except the last one, may be the original document, because each subsequent document will add some new features to the original document, and I may be interested in those new parameters.

So my job is to select the nth component of docpath based on some conditions, which is trivial if you write a script, stored procedure, or UDF using a T-SQL function. However, if you want to use the SELECT statement (you can imagine real-time e-commerce) to get the result of "on the fly", the task becomes very complicated.

Sub-chain help program

Assume an example string '2014/3/3/3/3/3/3/3/3/3/3/3/0/3/00/A/B/E/F /'. To find any component of this string, we can use a simple algorithm to select the substring between two consecutive locations of the delimiter:

• Member (1) = substring (string, 1, pos (1)-1 );
• Member (2) = substring (string, pos (1) + 1, pos (2)-pos (1)-1 );
• ...
• Member (n) = substring (string, pos (n-1) + 1, pos (N)-pos (n-1)-1 ),

The t_ SQL method is as follows:

• Member (1) = substring (string, 1, charindex ('/', String, 1)-1)
• Member (2) = substring (string, charindex ('/', String, 1) + 1, charindex ('/', String, charindex ('/', String, 1) + 1)-charindex ('/', String, 1)-1)
• And so on.

The spu_inddocid Stored Procedure (available in the downloaded file) generates a script that allows us to select this string from 1st to 31st any part. This process executes the algorithms I previously outlined and uses these parameters:

• @ Str-the name of the string, usually variable or column name.
• @ Level-this is actually a member's number or depth of recursion call.
• @ Prevpos and @ pos-I use these output parameters to save positions of delimiters and use them in the next procedure call.
• @ Answer-one more output parameter to accumulate the result.

Example

Run the findsource. SQL script to view the transaction chain example. The first part of the script creates a table named "documents" and loads the sample data in it. These are the potential rules for this situation:

• If the doctypeid of the first (leftmost) document in docpath is 1, the first document in docpath is the document source.
• If the doctypeid of the first document is 2, the document source is the second document in docpath.
• If the doctypeid of the first document is 3, the document source is the third document in docpath.

Therefore, the stored procedure sup_finddocid can generate related scripts for the first, second, and third documents in docpath.

Declare @ answer varchar (8000), @ prevpos varchar (3000 ),
@ POS varchar (3000)
Exec spu_finddocid 'docpath', 1, @ prevpos output,
@ POS output, @ answer output
Exec spu_finddocid 'docpath', 2, @ prevpos output,
@ POS output, @ answer output
Exec spu_finddocid 'docpath', 3, @ prevpos output,
@ POS output, @ answer output

Finally, use this script to view the original information of all unsuccessful transactions:

Select
Failed.doc ID [faileddoc],
Failed.doc parh,
Frst.doc ID [firstdoc],
Frst.doc typeid [first doctype],
Case
When frst.doc typeid = 1 then/* Copy generated script
For first member of docpath here */
When frst.doc typeid = 2 then/* Copy generated script
For second member of docpath here */
When frst.doc typeid = 3 then/* Copy generated script
For third member of docpath */
End sourcedoc
From
(Select docid, docparh
From documents where doctypeid in (7, 8) failed
Inner join
(Select docid, doctypeid from documents) frst
On frst.doc id = substring (docparh, 1,
Charindex ('/', docparh, 1)-1)

The following are the query results using my data:

faileddoc docparh firstdoc first doctype sourcedoc
10 1/5/7/10/1 1 1
11 3/7/9/11/3 3 9
12 2/6/8/12/2 2 6