存储过程,是在数据库服务端执行的批处理脚本。编译一次,执行多次,效率高;在服务端执行,安全;只传递存储过程名和参数名,减少网络流量。
#1.执行存储过程,参数有多个
EXEC sp_renamedb'HCMSACSDB','HCMSACSDB2'
#2.执行存储过程,参数有1个,返回result
print 'before execute sp_who'
declare @result int
EXEC @result = sp_who @loginame='sa'
print @result
print 'after execute sp_who'
#3.判断存储过程是否存在
if EXISTS (SELECT * FROM dbo.sysobjects where name = 'GetOrderInfoBetweenTime1andTime')
drop procedure GetOrderInfoBetweenTime1andTime
GO
#4.创建存储过程,并输出结果
create procedure GetOrderInfoBetweenTime1andTime
@startTime int,
@endTime int
as
select * from tbl_periodPara where iPeriod > @startTime and iPeriod < @endTime
GO
print 'before execute GetOrderInfoBetweenTimeandTime'
declare @result int
exec @result = GetOrderInfoBetweenTime1andTime 1, 721
print @result
print 'after execute GetOrderInfoBetweenTimeandTime'
GO
#5.创建存储过程,并设置返回码后返回
if EXISTS (SELECT * FROM dbo.sysobjects where name = 'GetOrderInfoBetweenTime1andTime2')
drop procedure GetOrderInfoBetweenTime1andTime2
GO
create procedure GetOrderInfoBetweenTime1andTime2
@startTime int,
@endTime int
as
declare @result int
set @result = 2
select MAX(iPeriod) from tbl_periodPara where iPeriod > @startTime and iPeriod < @endTime
print 'result is ' + CAST(@result as char(10))
return @result
GO
print 'before execute GetOrderInfoBetweenTimeandTime2'
declare @result int
exec @result = GetOrderInfoBetweenTime1andTime2 1,721
print @result
print 'after execute GetOrderInfoBetweenTimeandTime2'
GO