SQL stored procedures Use reference code

Stored procedures    use ebuy go -common system stored procedures   sp_addmessage  --store new user-defined error messages in sql  In the server DB instance   sp_helptext  --displays the actual text of the user-defined rule, default value, stored procedure, function, view, and so on   sp_xml_preparedocument   --reads the XML text provided as input, analyzes it using the MSXML parser, and provides the parsed document for use   sp_helpdb  ' ebuy '   --to view information for the specified database  sp_databases  --lists all databases on the server   sp_server_info  --lists server information, such as character sets, versions, and sequencing  sp_ stored_procedures  --list all stored procedures in the current environment  sp_configure  --modify sql server global configuration options  sp_ adduser --adding new users   sp_addrole  --to the current database create a new database role in the current database    --user-defined stored procedures   Create table users (  id int primary key,  name varchar (20)  not null,   pwd varchar ( not null )   Insert into  users values (1, ' Zhangsan ', ' zhang01 ')  go   create procedure adduser   --Creating a stored procedure (proCedure can be shortened to proc)    @id  int,   @name  varchar (,   @pwd  varchar (20)  as   begin   insert into users (ID,NAME,PWD)  values (@id, @ Name, @pwd)   end   exec adduser 2, ' Lisi ', ' lisi01 '   --Execute stored procedure ( The order in which the arguments are passed must be the same as the order in which the parameters correspond)    exec adduser  @name = ' Wangwu ', @pwd = ' wang01 ', @id =3  --execute the stored procedure , this sort of order can be inconsistent    declare  @v_id  int, @v_name  varchar, @v_pwd  varchar    set  @v_id =5  set  @v_name = ' zhaoliu2 '   set  @v_pwd = ' zhao022 '   exec  addUser  @v_id, @v_name, @v_pwd   --execute the stored procedure, call the stored procedure, and pass the variable arguments (the order must be consistent with the order in which the variables are defined)    Select  * from users   

   2 --Set default values  use Study  if exists (Select * from sys.all_ Objects where name= ' Increasecredit '  and type= ' P ')   drop procedure  increasecredit go   create proc increasecredit   @stuName  char (6) =null,   --Specifies that NULL is the default value    @credit  int = 3  --The integer value 3 is the default value  as   begin   if  @stuName =null    begin     print   ' No school number assigned, update not complete '      return    end  update  student set [email protected] where [email protected]  end   exec increasecredit  ' Boymatt ', 5  --provides all parameters   exec increasecredit  ' Boymatt '   -- Provides a partial parameter, the second parameter uses the default value 3   --Specify parameter direction   if exists (Select name from sys.all_ Objects where&nbSp;name= ' avgcreditformajor '  and type= ' P ')   drop proc avgCreditForMajor    create proc avgcreditformajor   @majorId  int,  --input Parameters    @avg  decimal (4,1)  output  --output Parameters  as  select  @avg =avg (Credit)  from  student where [email protected] go   declare  @avgCredit  decimal ( 4,1)     exec avgcreditformajor 1, @avgCredit  output  -- Save the return value of the output in the @avgcredit variable, and note that the output parameter must also be specified in the INPUT keyword   print (' Professional average credits: ' +cast (@avgCredit  as varchar)    --Displays the return value of the stored procedure  go  --using the return statement  use EBuy  if exists (select name  from sys.all_objects where name= ' checkcity '  and type= ' P ')   drop  proc avgcreditformajor   create proc checkcity   @cusid  varchar (20)  as  if (select addrEss from customer where [email protected])  like  '% Shanghai '     Return 1  else if (select address from customer where [email  Protected])  not like  '% Shanghai '  

   3    return 2  else    return 3   declare  @ret_status  int  exec  @ret_status =checkcity  ' 1001 '   --customers live in Shanghai   select  @ret_status  as  ' return status '    declare  @ret_status  int  exec  @ret_ status=checkcity  ' 1004 '   --customer does not live in Shanghai  select  @ret_status  as  ' return status '    declare  @ret_status  int  exec  @ret_status =checkcity  ' 13204 '   --customer non-existent situation  select  @ret_status  as  ' return status '   --Things and error handling  create proc proctest  as  declare  @num  int  select  @num =9  begin try    while  @num >0   begin    print convert (varchar,sqrt (@num))     select @[email protected]    if  @num =5    &nbsP; raiserror (' cardinality is less than 6 ', 16,1)    end  end try  begin catch    print  ' released the Wrong: ' +convert (Varchar,error_procedure ())    print  ' ERROR number: ' + Convert (Varchar,error_number ())    print  ' ERROR description: ' +convert (Varchar,error_message ())     print  ' Error level: ' +convert (varchar,error_severity ())    print  ' error is located at: ' +convert ( Varchar,error_line ())    print  ' ERROR status: ' +convert (Varchar,error_state ())   end  catch go   exec proctest  --Executing stored procedures     --transactions in stored procedures  use  EBuy  Create table account (  accid int,  type varchar (10),   balance decimal (10,2)  )  

   4  insert into account values (' savings ', +)  insert into  Account values (checking)    if exists (Select name from sys.all_ Objects where name= ' savingstochecking '  and type= ' P ')   drop proc  savingstochecking go  create proc savingstochecking   @accid  int,   @num  decimal (10,2)  as  declare  @bal  decimal (10,2)   begin try    select  @bal =balance from account where [email protected]  And type= ' savings '    if  @num > @bal     return 1    begin tran    update account set [email protected]  Where [email protected] and type= ' savings '     update account  set [email proteCted] where [email protected] and type= ' checking '    commit tran    return 0  end try  begin catch   rollback  tran   return 2  end catch go   declare  @retCode  int begin  exec  @retCode =savingstochecking 33,1500  if  @retCode = 0   print  ' transfer success '   else if  @retCode =1   print  ' Transfer amount is greater than book balance, transfer failed '   else    print  ' database error, transfer failed '  end   -- View, modify, and delete stored procedures   sp_helptext  ' savingstochecking '   --view stored procedures    alter proc  savingstochecking   @accid  int,  --Here you can modify the definition of a parameter    @num  decimal (10,2)    drop proc savingstochecking  --Delete Stored procedure    --recompile stored procedure  --1  

5 sp_recompile savingstochecking--2 create proc savingstochecking @accid int, @num decimal (10,2) with recompile- -When creating a stored procedure specify as ...--3 exec savingstochecking 33,1500 with recompile

SQL stored procedures Use reference code