一:存储过程的简单创建,修改与删除
1.创建简单的存储过程
use
AdventureWorks
go
create proc spEmployee
as
select * from HumanResources.Employee
go
create proc spEmployee
as
select * from HumanResources.Employee
执行上面的代码就创建了一个存储过程
如果想运行这个存储过程
可以直接执行exec spEmployee这个语句
2.更改存储过程
ALTER
proc
[
dbo
]
.
[
spEmployee
]
as
select top 13 * from HumanResources.Employee
as
select top 13 * from HumanResources.Employee
3.删除存储过程
二:存储过程的输入参数和输出参数
1.有输入参数的存储过程
查看该存储过程的结果可以用
exec spEmployee '123'
或直接exec spEmployee
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
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.有输出参数的存储过程
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
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
exec @myval = spEmployee @myval output
print @myval
第二个测试该存储过程的语句
declare
@myval
nvarchar
(
50
)
set @myval = ' xland '
exec @myval = spEmployee @myval output
print @myval
set @myval = ' xland '
exec @myval = spEmployee @myval output
print @myval
输出xland 456
三:用户定义函数
1.返回标量值的用户定义函数
先做一个简单的日期处理函数
把长日期缩短成短日期
1.返回标量值的用户定义函数
先做一个简单的日期处理函数
把长日期缩短成短日期
Create
function
dbo.DayOnly(
@date
datetime
)
returns varchar ( 12 )
as
begin
return convert ( varchar ( 12 ), @date , 101 )
end
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
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 ())
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 + ' % ' )
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 ' )
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
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
)
