存储过程和用户自定义函数.docVIP

存储过程和用户自定义函数 一:存储过程的简单创建,修改与删除1.创建简单的存储过程 use?AdventureWorksgocreate?proc?spEmployeeasselect?*?from?HumanResources.Employee 执行上面的代码就创建了一个存储过程 如果想运行这个存储过程 可以直接执行exec spEmployee这个语句 2.更改存储过程 ALTER?proc?[dbo].[spEmployee]asselect?top?13?*?from?HumanResources.Employee 3.删除存储过程 drop?proc?dbo.spEmployee 二:存储过程的输入参数和输出参数1.有输入参数的存储过程 use?AdventureWorksgocreate?proc?spEmployee????@LastName?nvarchar(50)?=?nullasif?@LastName?is?null????select?top?13?*?from?HumanResources.Employeeelse????select?top?10?*?from?HumanResources.Employee 查看该存储过程的结果可以用exec spEmployee 123或直接exec spEmployee 存储过程的重载...2.有输出参数的存储过程 use?AdventureWorksgoalter?proc?spEmployee????@LastName?nvarchar(50)?=?null?outputasif?@LastName?is?null????begin????????print?null????????return?123????endelse????begin????????print?@LastName????????return?456????end 看第一个测试该存储过程的语句 declare?@myval?nvarchar(50)exec?@myval?=?spEmployee?@myval?outputprint?@myval 输出null? 123 第二个测试该存储过程的语句 declare?@myval?nvarchar(50)set?@myval?=?xlandexec?@myval?=?spEmployee?@myval?outputprint?@myval 输出xland? 456 三:用户定义函数1.返回标量值的用户定义函数先做一个简单的日期处理函数把长日期缩短成短日期 Create?function?dbo.DayOnly(@date?datetime)returns?varchar(12)asbegin?????return?convert(varchar(12),@date,101)end 为了测试上面的函数先做个脚本 use?Accountingdeclare?@counter?intset?@counter?=?1while?@counter?=?10begin????insert?into?Orders?values(1,dateadd(mi,@counter,getdate()),1)????set?@counter?=?@counter?+1end? 然后检索这个脚本 新插入的数据记录 use?Accountingselect?*?from?orders?where?dbo.DayOnly(date1)?=?dbo.DayOnly(getdate()) 2.返回表的用户定义函数先看例子 use?AdventureWorksgocreate?function?dbo.fnContactSearch(@LastName?nvarchar(50))returns?tableasreturn?(select?*?from?Person.Contact?where?LastName?like?@LastName+%) 执行这个例子 use?AdventureWorksselect?*?from?fnContactSearch(Ad) 3.综合例子:返回表,有输入参数 use?xlandgocreate?function?dbo.funGetMytable????(@id?as?int)????returns?@allrows?table????(????????id??int?not?null,????????title??nvarchar(max)?null????)asbegin????inse