SQL Implementation of recursive and In () parameter transfer solution In Stored Procedures

1. SQL Recursion

In SQL Server, we can use table expressions to implement recursive algorithms, which are generally used to prevent organizations from loading and processing their correlations.

--> Implementation:

Assume that the three main fields in the organization table are OrganiseUnitID, ParentOrganiseUnitID, and OrganiseName)
Copy codeThe Code is as follows:
With organise
(Select * from OrganiseUnit where OrganiseUnit. OrganiseUnitID = @ OrganiseUnitID
Union all select OrganiseUnit. * from organise, OrganiseUnit
Where organise. OrganiseUnitID = OrganiseUnit. ParentOrganiseUnitID)

Select OrganiseName from organise

The preceding SQL statement is used to input the primary key ID of an organizational unit and query the name of the corresponding organizational unit and the names of all its subordinate organizational units.

2. In parameter transfer during stored procedures

--> Scenario

① Using the SQL recursion method just now, we can query an organizational unit and all its subordinate units. Assume that each organizational unit has another field OrganiseCode );

② When we need to filter data according to the organization code, we will use the In query condition, for example, select * from OrganiseUnit where OrganiseCode in ('20160301', '20160301 ', '123 ')

③ But the conditions in () cannot always be fixed. Sometimes we need to pass in with parameters. We may think of setting a variable parameter @ OrganiseCode, and then following '123 ', isn't the formatting parameter of '123' and '123' enough?

④ When using a parameter, in will forcibly convert the parameter type to be consistent with the condition field, and cannot construct a string (if the field itself is varchar or char type, in is equivalent to only one condition value, instead of a group)

--> Implementation

① Exec can be used to execute the entire SQL statement as a parameter. For example, exec ('select * from OrganiseUnit where OrganiseCode in ('+ @ OrganiseCode + ')'); in this way, the modification of the stored procedure is complex and there is no anti-injection function.

② We adopt another solution. First, write an SQL function to split strings.
Copy codeThe Code is as follows:
Create function SplitIn (@ c varchar (2000), @ split varchar (2 ))
Returns @ t table (col varchar (20 ))
As
Begin
While (charindex (@ split, @ c) <> 0)
Begin
Insert @ t (col) values (substring (@ c, 1, charindex (@ split, @ c)-1 ))
Set @ c = stuff (@ c, 1, charindex (@ split, @ c ),'')
End
Insert @ t (col) values (@ c)
Return
End

We pass in a string and a separator for this function, which can split the string by the specified symbol and return the query result.

For example, execute select col from SplitIn ('1996, 10000001,100 then 003 ',',')

Return Value:

10000001

10000002

10000003

③ With this function, we have a new solution.

Define the parameter @ OrganiseCode to input a string. this parameter is composed of one or more organisecodes, separated by commas;

Call method: select * from OrganiseUnit where OrganiseCode in (select col from SplitIn (@ OrganiseCode ,','))