sql server存储过程学习

一、新建并进入数据库

create database Lambor

use Lambor

二、新建数据表并插入数据

create table People(
    id int primary key,
    name nvarchar(20),
    age int,
    birthday datetime
)

insert into People(id,name,age,birthday) values(1,'Amanda',1,GETDATE())
insert into People(id,name,age,birthday) values(2,'Balea',18,GETDATE())

select * from People

 

三、编写存储过程

（1）建立名为GetPeopleName的无参存储过程

create procedure GetPeopleName
as
begin
    select name from People
end
--执行
execute GetPeopleName

运行结束：

 

（2）有返回值的存储过程

create procedure AddPeople
as
begin
    insert into People(id,name,age,birthday) values(6,'Buffer',22,GETDATE());
    return 1; 
end
--常规执行语句，会提示“命令一成功执行”，但不会输出执行结果
execute AddPeople
--可输出执行结果的执行方式
USE [Lambor]
GO
DECLARE    @return_value int
EXEC    @return_value = [dbo].[AddPeople]
SELECT    'Return Value' = @return_value
GO

方式1运行结果：

 由此可以看出，常规的执行方式，输出窗口并不会打印返回值

方式二运行结果：

 

 

 （3）有入参的存储过程

create procedure GetPeople
@PeopleId int = 1 -- =1是指为参数添加默认值，可以不写
as
begin
    select * from People where id = @PeopleId
end
--两种执行方式
-----1.不输入参数，则系统会使用默认值
execute GetPeople
-----2.输入参数
execute GetPeople 2

无参（使用默认参）运行结果

 有参运行结果：

 

（4）有输入、输出参数的存储过程

create procedure GetPeople_Out
@PeopleId int,
@Ret nvarchar(30) output
as
begin
    if(@PeopleId < 1 and @PeopleId > 99)
    begin
        set @Ret = 'Fail';
    end 
    else
    begin
        select * from People where id = @PeopleId;
        Set @Ret='Success';
    end
end
--执行方式1
execute GetPeople_Out 1,null
--执行方式2(显示输出值)
Declare @Ret nvarchar(30)
execute GetPeople_Out 2,@Ret = @Ret output
select @Ret as N'@Ret'

方式1运行结果：

 方式2运行结果：

 

（5）有输入输出参数和结果集的存储过程

create procedure GetPeople_DS
@PeopleId int,
@Ret nvarchar(30) output
as 
begin
    if(@PeopleId < 1 and @PeopleId > 99)
    begin
        select @Ret = name from People where id = @PeopleId
    end
    else
    begin
        set @Ret ='Fail'
    end
    select * from People
end

execute GetPeople_DS 1,null

运行结果：

 

（6）存储过程中创建变量、赋值变量、创建表变量和临时表
--返回多个结果集(多个语句中可用‘；’分割，也可不用)

create procedure GetPeople_Ext
@PeopleId int
as
begin
    declare @Var nvarchar(10); --定义变量
    set @Var ='123';
    --定义表变量
    declare @PeopleTab table
    (
        ID int not null primary key,
        name nvarchar(30) not null,
        age int
    )
    --表变量只能在定义的时候添加约束
    --定义临时表
    create table #Tab
    (
        ID int not null primary key,
        name nvarchar(30) not null,
        age int
    );
    --临时表可以创建之后添加约束
    alter table #Tab add constraint S_UNIQUE unique(Name);

    --判断入参是否有效，if有效则向表变量插入语数据
    if(@PeopleId > 0)
    begin
        --从现有表中取出数据插入表变量
        insert into @PeopleTab(id,name,age) 
        select id,name,age from People where id = @PeopleId;

        --从现有表取出数据插入临时表
        insert into #Tab(ID,Name,age)
        select id,name,age from People where id = @PeopleId
    end
    select * from @PeopleTab
    select * from #Tab
end
--执行存储过程
execute GetPeople_Ext 1

运行结果：

 

（7）存储过程执行动态sql
--在where中拼接int型参数

先看个错误示范：

create procedure GetPeople_Dynamic
@PeopleId int
as
begin
    declare @Sql nvarchar(MAX)
    if(@PeopleId > 0 and @PeopleId < 99)
    begin
        set @sql ='select * from People where id ='+ @PeopleId
    end
    --执行动态sql
    exec (@Sql)
end

报错提示：varchar转int失败  -----错误原因：无法直接让string与int相加

 

 正确写法：

create procedure GetPeople_Dynamic
@PeopleId int
as
begin
    declare @Sql nvarchar(MAX)
    if(@PeopleId > 0 and @PeopleId < 99)
    begin
        --使用 cast()把int转为 varchar，这是+不表示相加，转为表示字符串连接
        set @sql ='select * from People where id ='+CAST(@PeopleId as varchar)
    end
    --执行动态sql
    exec (@Sql)
end

运行结果：

 

 

（8）存储过程动态sql中的string类型拼接（与上述7中的int类型做区分）

alter procedure GetPeople_Dynamic1
@Name varchar(30)
as
begin
    declare @Sql nvarchar(MAX)
    if(@Name is not null and @Name <> '')
    begin
        --在sql server中'表示转义，一个内容为单引号的字符串为：''''  
        --(第一第四个表示内容是字符串，第二个表示转义，第三个是内容本身)
        set @Sql ='Select * from People where name ='+''''+ @Name+'''';
    end
    execute (@Sql)
end

运行结果：

（9）存储过程参数为自定义表类型

--先创建一个自定义表类型 OwnerType
create type  OwnerType AS Table(
    name nvarchar(100)
)
--创建一个存储过程，参数类型为上述的自定义表类型OwnerType
create procedure TypeParam
@PeopleName as OwnerType readonly,
@Sql nvarchar(MAX) output
as
begin
    --if没有这句，执行之后@sql的值为null
    Set @Sql ='';
    select @Sql = @Sql + '||' + name + '||' from  @PeopleName;
end

--执行存储过程，输出@Sql的值
DECLARE    @Sql nvarchar(MAX),
        -- 定义表类型参数
        @OwnerType as OwnerType
--为表类型参数赋值
insert into @OwnerType values('你好'); 
insert into @OwnerType values('世界'); 
EXEC [dbo].[TypeParam]
        @PeopleName = @OwnerType,
        @Sql =@Sql output

SELECT    'Return Value' = @Sql

执行结果：