【网学网提醒】：网学会员为需要朋友们搜集整理了SQLServer存储过程的基本概念以及语法相关资料，希望对各位网友有所帮助!
SQLServer存储过程的基本概念以及语法【转】
存储过程的概念SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程是SQL语句和可选控制流语句的预编译集合，存储在数据库中，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。在SQLServer中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列SQL语句。可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，而且减少网络通信的负担。安全性更高。创建存储过程在SQLServer中，可以使用三种方法创建存储过程：①使用创建存储过程向导创建存储过程。②利用SQLServer企业管理器创建存储过程。③使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。下面介绍使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几个事项：①不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。②存储过程可以嵌套使用，嵌套的最大深度不能超过32层。③创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。④存储过程是数据库对象，其名称必须遵守标识符规则。⑤只能在当前数据库中创建存储过程。⑥一个存储过程的最大尺寸为128M。
使用CREATEPROCEDURE创建存储过程的语法形式如下：
QUOTE:CREATEPROC[EDURE]procedure_name[;number][;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]ASsql_statement[...n]用CREATEPROCEDURE创建存储过程的语法参数的意义如下：
procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。
例如：下面创建一个简单的存储过程productinfo，用于检索产品信息。USENorthwindifexists(selectnamefromsysobjectswherename='productinfo'andtype='p')dropprocedureproduct
infoGOcreateasselect*fromproductsGO通过下述sql语句执行该存储过程：executeproductinfo即可检索到产品信息。执行存储过程procedureproductinfo
直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}alue|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE][[@parameter=]{v
使用EXECUTE命令传递单个参数，它执行showind存储过程，titles为参数值。以showind存储过程需要参数(@tabname)，它是一个表的名称。其程序清单如下：EXECshowindtitles当然，在执行过程中变量可以显式命名：EXECshowind@tabname=titles如果这是isql脚本或批处理中第一个语句，则EXEC语句可以省略：showindtitles或者showind@tabname=titles
下面的例子使用了默认参数USENorthwindGOCREATEPROCEDUREinsert_Products_1(@SupplierID_2@CategoryID_3ASINSERTINTOProducts(ProductName,SupplierID,CategoryID)VALUES(@ProductName_1,@SupplierID_2,@CategoryID_3)GOexecinsert_Products_11,1Select*fromProductswhereSupplierID=1andCategoryID=1GOint,int,
@ProductName_1nvarchar(40)='无')
下面的例子使用了返回参数USENorthwindGOCREATEPROCEDUREquery_products(ASselect@ProductName_2=ProductNamewhereSupplierID=@SupplierID_1执行该存储过程来查询SupplierID为1的产品名：declare@productnvarchar(40)execquery_products1,@productoutputselect'产品名'=@productgofromproducts@SupplierID_1int,@ProductName_2nvarchar(40)output)
查看存储过程存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。
使用企业管理器查看用户创建的存储过程在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。
使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_help[[@objname=]name]参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表
修改存储过程
存储过程可以根据用户的要求或者
基表定义的改变而改变。使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]
重命名和删除存储过程
1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename原存储过程名称，新存储过程名称另外，通过企业管理器也可以修改存储过程的名称。
删除存储过程
删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropprocedure{procedure}[,…n]当然，利用企业管理器也可以很方便地删除存储过程。
存储过程的重新编译在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQLServer提供三种重新编译存储过程的方法：1、在建立存储过程时设定重新编译语法格式：CREATEPROCEDUREprocedure_nameWITHRECOMPILEWITHOBJECTRECOMPILEASsql_statement2、在执行存储过程时设定重编译语法格式：EXECUTE语法格式为：EXECprocedure_namesp_recompile3、通过使用系统存储过程设定重编译
系统存储过程与扩展存储过程
1.系统存储过程系统存储过程存储在master数据库中，并以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查
看数据库对象信息的系统存储过程sp_help、显示存储过程和其它对象的文本的存储过程sp_helptext等。
2.扩展存储过程：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用Transact-SQL过程那样从Transact-SQL语句调用这些函数。例:利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。执行代码：usemasterexecxp_cmdshell'dir*.exe'执行结果返回系统目录下的文件内容文本信息。最后给大家举一个例子：
QUOTE:/**1、在Northwind数据库中，创建一个带查询参数的存储过程，要求在输入一个定购金额总额＠total时，查询超出该值的所有产品的相关信息，包括产品名称和供应商名称、单位数量、单价、以及该产品的定购金额
总额，并通过一个输出参数返回满足查询条件的产品数**/
IFexists(select*fromSysObjectswherename='more_than_total'andtype='p')dropproceduremore_than_totalgoCREATEPROCEDUREMore_Than_Total@totalmoney=0ASDeclare@amountsmallintBEGINselectdistinctP.productName,S.contactName,P.UnitPricefromProductsPinnerjoin[orderDetails]O
onp.productID=o.productIDinnerjoinsupplierssonp.supplierID=s.SupplierIDwhereO.productIDin(selectproductIDfrom[orderDetails]groupbyproductIdhavingsum(quantity*unitprice)>@total)ENDGO
