SQL implementation recursion and stored procedure in () parameter delivery solution [GO]

SQL implementation recursion and in-store in () parameter delivery solution 1.SQL recursion in SQL Server, we can use table expressions to implement recursive algorithms, which are generally used to prevent the loading of institutions and related processing. Implementation: Assume that the main three fields in the Organiseunit (Organization table) are Organiseunitid (organization primary key ID), Parentorganiseunitid (organization parent ID), Organisename (organization name)?

1234567

[sql] with organise as(select * from OrganiseUnit where OrganiseUnit.OrganiseUnitID = @OrganiseUnitID   union all select OrganiseUnit.* from organise, OrganiseUnit   where organise.OrganiseUnitID = OrganiseUnit.ParentOrganiseUnitID)    select OrganiseName from organise

The above SQL statement is implemented, passing in the organization's primary key ID, querying its corresponding organization name and all its subordinate organization name. 2. The in parameter in the stored procedure---scenario ① through just the SQL recursion, we can already query an organization and all its subordinate units, assuming that each organization also has a field Organisecode (organization code); ② When we need to filter the data according to the organization code, we use in this query condition, such as SELECT * from Organiseunit where Organisecode in (' 10000001 ', ' 10000003 ', ' 10000002 ') ③ but in () the conditions cannot always be fixed, and sometimes we need to pass in parameters; we might think of setting a variable parameter @organisecode and then following ' 10000001 ', ' 10000003 ', ' 10000002 ' The format of the spelling parameter is not OK? ④in The parameter type is cast in accordance with the condition field, the construction string is not supported (if the field itself is a varchar, char type, in equivalent to only one condition value instead of a set)--implementation ① can use Exec to execute the entire SQL as a parameter. For example: EXEC (' select * from Organiseunit where organisecode in (' [email protected]+ ') '), so that the stored procedure is complex and has no anti-injection capability. ② we use another solution, first write a SQL function, the function is to split the string?

12345678910111213

[sql] createfunctionSplitIn(@c   varchar(2000),@split   varchar(2))     returns@t   table(col   varchar(20))     asbegin  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)       returnend

We pass in the string and delimiter for this function, and he can split the string by the specified symbol and return it as the query result. For example: Execute select col from Splitin (' 10000001,10000002,10000003 ', ', ') return: 100000011000000210000003③ with this function, We have a new solution. Defines the parameter @organisecode, for which the string is passed, the parameter is composed of one or more organisecode, the middle with "," split; Call mode:?

1	select* from OrganiseUnit where OrganiseCode in (select col fromSplitIn(@OrganiseCode,‘,‘))

SQL implementation recursion and stored procedure in () parameter delivery solution [GO]