Stored Procedure Learning (1)

During the course "Database", the teacher talked about the "Stored Procedure", but he never wrote it by himself, and he was not familiar with the syntax. Now the company wants to upgrade the ERP system. This task falls on me. I modified some of them some time ago.ProgramFunction, but database operations are always usedCodeJoin SQL. Later, I saw on the Internet that this method is easy to inject, and the processing efficiency of a large amount of data is very low, so I am considering replacing all the previous operations with stored procedures.

The table to be processed this time is relatively simple:

Table Name atr_win_m

The main functions are as follows:

1. check whether there are records based on the id_win_ B and clm_filed fields.

2. If yes, update it. If no, insert it.

3. During the insert operation, you must call another Stored Procedure pr_amt_max_no to generate an ID number.

4. After the insertion is complete, call the Stored Procedure pr_amt_max_update to update the corresponding field values in the atm_max_no table.

 

For the sake of simplicity, we will not list the details of the two stored procedures in the table atm_max_no and above.

 

Bytes ----------------------------------------------------------------------------------------------

The two stored procedures pr_amt_max_no and pr_amt_max_update both use the result set returned by select (although it only contains two or three fields ), according to information on the Internet, a stored procedure cannot access the result set generated by another stored procedure.

Bytes ----------------------------------------------------------------------------------------------

Bytes ---------------------------------------------------------------------------------------------

Solution: It seems that the above two stored procedures cannot be directly judged and called in this stored procedure, so the function has been modified as follows:

Check whether there are corresponding records based on the id_win_ B and clm_filed fields. If yes, update and return 0. If no, return 1. Then, process it in the CS code.

The stored procedure is as follows:

/* Note: <br/> This stored procedure is used to update the art_win_m table. <br/> in this procedure, the filedname parameter win_id and filedstr are used to query whether the corresponding records exist, if yes, update and return 0. If NO, 1 is returned. <br/> filedstr content: "clm_filed, clm_cap, clm_order, clm_width, clm_vis, clm_input "<br/> */</P> <p> Create procedure [DBO]. [pr_atr_win_m_update] <br/> @ win_id char (10), -- window id <br/> @ id_win_m char (10 ), -- ID of the newly created record <br/> @ username char (10), -- current username <br/> @ userdate datetime, -- time <br/> @ filedstr varchar (200) -- contains Field Information The string format of interest is "a, B, c, d "<br/> as <br/> declare @ positiona int -- pointer 1 <br/> declare @ positionb int -- pointer 2 <br/> declare @ countno int -- Query number of records <br/> declare @ filedname varchar (20) -- store the obtained field name <br/> declare @ ccaption varchar (20) -- display name <br/> declare @ Corder int -- column order <br/> declare @ cwidth int -- column width <br/> declare @ cvis bit -- visible <br/> declare @ cinput varchar (20) -- data display format: 99,999.99 </P> <p> set @ positiona = 0 <br /> Set @ positionb = 0 <br/> set @ countno = 0 <br/> set @ countno = 0 <br/> begin transaction <br/> set @ positiona = charindex (', ', @ filedstr) -- get the location of the first bean number <br/> set @ filedname = substring (@ filedstr, @ positionb + 1, @ positiona-@ positionb-1) -- Obtain the field name string, which is in front of Pa <br/> set @ positionb = charindex (',', @ filedstr, @ positiona + 1) -- Second bean number location <br/> set @ ccaption = substring (@ filedstr, @ positiona + 1, @ positionb-@ positiona-1) -- display Name, Pb in front <br/> set @ positiona = charindex (',', @ filedstr, @ positionb + 1) -- Position of the third bean number <br/> set @ Corder = cast (substring (@ filedstr, @ positionb + 1, @ positiona-@ positionb-1) as INT) -- sequence number, pa front <br/> set @ positionb = charindex (',', @ filedstr, @ positiona + 1) -- position of the fourth bean number <br/> set @ cwidth = cast (substring (@ filedstr, @ positiona + 1, @ positionb-@ positiona-1) as INT) -- width, PB front <br/> set @ positiona = charindex (',', @ filedstr, @ positi Onb + 1) -- location of the fifth bean number <br/> set @ cvis = cast (substring (@ filedstr, @ positionb + 1, @ positiona-@ positionb-1) as bit) -- display, Pa in front </P> <p> set @ cinput = substring (@ filedstr, @ positionb + 1, Len (@ filedstr )) -- display format </P> <p> select @ countno = count (*) from atr_win_m where id_win_ B = @ win_id and clm_filed = @ filedname </P> <p> If (@ countno! = 0) -- there is a record. Maybe it is more appropriate to use @ countno = 1? <Br/> begin <br/> Update art_win_m set user_name = @ username, user_date = @ userdate, clm_filed = @ filedname, <br/> clm_cap = @ ccaption, clm_order = @ Corder, clm_width = @ cwidth, clm_vis = @ cvis, clm_input = @ cinput <br/> return (0) <br/> end <br/> else <br/> return (1) <br/> go <br/>