mssql 存储过程入门与实例应用

mssql 存储过程入门与实例应用
/*

首先来看看mssql 存储过程创建

create procedure proc_stu
　　as
　　select * from student
go

创建一个过程：例子
下面的语句创建的架构中的人力资源程序remove_emp：

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps教程 NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
 END;

 
 关于存储过程简单实例看了，那么我们来看语法
 

 CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

好我们来看一个实例应用中的实例，
查询id为1的记录用存储过程实例

@Total int OUTPUT
-----------------------------------
SET @Sql=N'select a,b,c,d from t where id=1'
Exec sp_executesql @Sql, N'@Total Int Out',@Total Out
-----------------------------------
Return @Total

实例三

加入一笔记录到表book,并查询此表中所有书籍的总金额 
  

   Create proc insert_book
     @param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
     with encryption ---------加密
     as
     insert book(编号,书名，价格） Values(@param1,@param2,@param3)
     select @param4=sum(价格) from book
     go
     执行例子:
     declare @total_price money
     exec insert_book '003','Delphi 控件开发指南',$100,@total_price
     print '总金额为'+convert(varchar,@total_price)
     go

*/