学习SQL Server存储过程的记录（待更新）

SQL Server 的存储过程

一、常见系统存储过程方法

 

exec sp_databases
​
---删除自定义存储过程---
drop proc usp_getProvinceList  
---显示存储过程的文本（sql语句）---
exec sp_helptext'usp_getProvinceList'

二、创建存储过程

 

1.简单的创建存储过程

create proc/procedure usp_gelloworld
as
begin
    print 'hello world'
end

注意：

1.proc/procedure二选一；usp_gelloworld是存储过程的名字

2.begin``end相当于方法的一对大括号{}

2.调用存储过程

exec usp_gelloworld

3.删除\修改存储过程

-------修改------
alter proc usp_getProvinceList
as
begin
    select * from dbo.new_province
end
-------删除------
drop proc usp_getProvinceList
as
begin
    select * from dbo.new_province
end

4.创建带两个参数的存储过程

举例1:参数为int类型
create proc usp_add_number
@n1 int,
@n2 int
as
begin
    select @n1 + @n2
end
----执行有参数的存储过程----
exec usp_add_number 100,200
​
举例2:当有默认值时
alter proc usp_add_number
@n1 int=100,
@n2 int
as
begin
    select @n1 + @n2
end
----执行有参数的存储过程--- 
注意：
    1.如果是第一个参数有默认值，在调用存储过程时必须指明传入参数是哪个
    2.可以有多个参数带默认值
----
exec usp_add_number @n2=100

举例2:参数为字符串和int
create proc usp_select_contract_by_conditiom
@name nvarchar(100),
@status int
as
begin
    select * from dbo.new_contractBase where new_name = @name and new_approvalstatus = @status
end
----执行有参数的存储过程----
示例1:exec usp_select_contract_by_conditiom '合同1',40
示例2:exec usp_select_contract_by_conditiom @name='合同1',@status=40

5.带输出参数的存储过程

--- 带输出参数的存储过程 --
--- 当在存储过程中需要返回多个值的时候，就可以使用输出参数来返回这些值
alter proc usp_show_contract
@status int,
@recordcount int output  -- 输出参数，可以有多个
as
begin
    select * from dbo.new_contractBase where new_approvalstatus = @status
    -- 把查询结果的条数赋值给变量 @recordcount
    set @recordcount = (select count(*) from dbo.new_contractBase where new_approvalstatus = @status)
end
​
declare @rc int  -- 定义变量 @rc
exec usp_show_contract 10,@rc output  -- 注意输出参数指定 output
print @rc  -- 打印参数

6.带输出参数的分页查询的存储过程

--创建一个分页查询的存储过程
create proc get_contractByPage
@pageSize int,--每页大小
@pageIndex int,--第几页（页数）
@recordCount int output,--返回值 总条数
@pageCount int output--返回值  总页数
as
begin
    select 
    c.new_contractId,
    c.new_name,
    c.new_address,
    c.new_amount
    from (select *,rn = ROW_NUMBER() over (order by new_contractId asc) from dbo.new_contractBase) as c
    where c.rn between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
​
    --计算总条数
    set @recordCount = (select count(*) from dbo.new_contract)
    --计算总页数
    set @pageCount = CEILING(@recordCount*1.0/@pageSize) --之所以乘以1.0，是因为 “/” 结果要向上取整
end
​
declare @rc int --定义输出参数 总条数
declare @pc int --定义输出参数 总页数
exec get_contractByPage @pageSize=3,@pageIndex=2,@recordCount=@rc output,@pageCount=@pc output
print @rc
print @pc