SQL Proc (Stored procedure)/tran (things)

Stored procedures are like C # methods

1. The thing is written in the process, directly call the stored procedure

1.1 Procedure with no parameters

/*transaction things, procedure stored procedures */create proc copytable_1_10000 asbegin tran--start things declare @tran_error int;--declaration parameter SET @ tran_error=0;--Assigning a value to a parameter declare @i int,@y int;set @i=10000;set @y=1;/* A new table does not exist, copy data to a new table select * Into Table_3 from (select*from Table_1) as A*/while @y<@i   --Cycle begin/   * New table exists, copy data *   /INSERT INTO Table_3 (materialname,mtype) Select Materialname,mtype from Table_1   set @[email protected]+1;--loop condition   set @[email protected][email protected] @ERROR ;--the system parameter that the thing used to record the error end/* to determine if the execution of the thing is wrong */if (@tran_error >0) [email protected]_error greater than 1 means error, things roll back begin rollback Tran; print ' things rollback ' endelsebegin commit tran print ' commit thing ' end
--Call the stored procedure exec copytable_1_10000

1.2 process with the transfer of parameters

--proc with parameters create proc Showdescription@mtype int--The parameters to be passed Asbeginselect * into  #table_3 from (select Table_1. Materialname,table_2.mtypedescription from Table_1 left join table_2 on  table_1.mtype=table_2.id where [email Protected]) as C select * FROM #table_3end--call, pass @mtype parameter exec showdescription @Mtype =2--Delete drop proc Showdescription

　　

SQL Proc (Stored procedure)/tran (things)