Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to transfer In parameters in SQL stored procedures". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.SQL recursion

In SQL Server, we can use table expressions to implement recursive algorithms, which are generally used to prevent mechanism loading and correlation processing.

-- > implement:

Suppose the three main fields in OrganiseUnit (organization table) are OrganiseUnitID (organization primary key ID), ParentOrganiseUnitID (organization parent ID), and OrganiseName (organization name). The copy code is as follows: 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 by passing in the organization primary key ID and querying its corresponding organization name and all its subordinate organization names.

two。 Passing In parameters in stored procedures

-> scenario

① through the SQL recursion just now, we can already query an organization and all its subordinate units; suppose that each organization also has a field of OrganiseCode (organization code)

② when we need to filter data according to the organization code, we use In as a query condition, such as select * from OrganiseUnit where OrganiseCode in.

③ but the conditions in in () cannot always be fixed, and sometimes we need to pass them in with parameters; we may think of setting a variable parameter @ OrganiseCode, and then spelling the parameters according to the format of '10000001', '10000003', '10000003', '10000002', isn't it?

When using parameters, ④ in forces the parameter type to be the same as the conditional field, and does not support the construction of strings (if the field itself is varchar or char, then in is equivalent to only one conditional value, not a set)

-- > implementation

① can use exec to execute the entire sql as a parameter, for example: exec ('select * from OrganiseUnit where OrganiseCode in (' + @ OrganiseCode+')'); so the modification of the stored procedure is complex and has no anti-injection function.

②, let's use another solution, first write a SQL function. The function is to split the string and copy the code 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 (@ cmem1charindex (@ split,@c)-1) set @ c = stuff (@ cmem1charindex (@ split,@c),'') end insert @ t (col) values (@ c) return end

We pass a string and delimiter to this function, and it can split the string by the specified symbol and return it as a query result.

For example: execute select col from SplitIn ('10000001pr. 10000002pr 10000003mr.

Return:

10000001

10000002

10000003

With this function in ③, we have a new solution.

Define the parameter @ OrganiseCode, passing in a string, which consists of one or more OrganiseCode, separated by ","

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

This is the end of the content of "how to pass In parameters in SQL stored procedures". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.