基本存储过程

存储过程，是在数据库服务端执行的批处理脚本。编译一次，执行多次，效率高；在服务端执行，安全；只传递存储过程名和参数名，减少网络流量。

#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