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