存储过程

--自定义创建不带参数的存储过程
use MySchool
create procedure usp_student
as
select *from Student

--查看

exec usp_student

--自定义创建带参的存储过程
create proc usp_result
@Grade int =60
as
select StuName,Grade from Student, Result
where Student.StuNo=Result.StuNo
and grade>=@Grade
go

--查看
exec usp_result 80

--自定义创建带参的存储过程并且判断参数输入是否符合要求

create procedure usp_getScore
@score int=60
as
if(@score>0 and @score<100)    --判断参数是否符合要求
begin
select StuName,Grade
from student,Result
where dbo.Student.StuNo=dbo.Result.StuNo
and Grade>=@score
end
else
begin
raiserror('及格线有误',16,1)
end

--查看存储过程
exec usp_getScore -80

--创建分页查询存储过程
create proc usp_score
@pageindex int , --当前页数
@pagesize int, --每页记录数
@totalrecords int output, --总记录数
@totalpages int output --总页数
as
select *from
(select *,ROW_NUMBER() over(order by StuNo) as id from Student)
as temp
where id between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
--总记录数
select @totalrecords =COUNT(1) from Student
--总页数
set @totalpages=CEILING(@totalrecords*1.0/@pagesize)

--查看
declare @totalnum int
declare @totalnumber int
exec usp_score 1,4,@totalnum output,@totalnumber output
print '总记录数'+convert(nvarchar(32), @totalnum)
print '总页数'+convert(nvarchar(32) ,@totalnumber)

--扩展存储过程
use master
go
exec sp_configure 'show advanced options',1 --显示高级配置信息
go
peconfigure --重新配置
go

exec sp_configure 'xp_cmdshell',1 --打开xp_cmdshell选项
go
peconfigure --重新配置
go

--创建数据库bankDB,要求保存在D:ank目录下
exec xp_cmdshell 'md D:ank',no_output --创建文件夹D:ank
if exists(select *from sysdatabases where name='bank')
drop database bankDB
go
create database bankDB
on
(
name='bankDB_data',
filename='D:ankankDB_data.mdf',
size=3mb,
maxsize=10mb,
filegrowth=10%
)
log on
(
name='bankDB_log',
filename='D:ankankDB_log.ldf',
size=3mb,
filegrowth=10%
)