SQL存储过程

SQL存储过程

一、存储过程的理解

一般来说存储过程可以理解为可以在SQL中执行的一段程序。
存储过程在创建时编译，每次执行时都不需要再编译，而一般的SQL语句每次执行一次，编译一次。
存储过程可以针对特定用户设定使用权限。

二、存储过程简单运用

测试数据：

    create table Student(
        S_id int identity   
        ,S_name nvarchar(20)
        ,S_age tinyint
        ,S_Sex nchar(1)
    )
    insert into Student(S_name,S_age,S_sex) 
            select '杨过'  ,20,'男' union all
            select '欧阳锋',40,'男' union all
            select '小龙女',17,'女' union all
            select '梅超风',26,'女' union all
            select '一灯大师',50,'男' union all
            select '田伯光',35,'男'

创建无参存储过程

    create procedure Pro_Student
    as 
    begin
        select * from Student
    end
    go

    exec Pro_Student  --执行存储过程

结果显示

创建有参的存储过程

    If exists(select * from sysobjects where name = 'Pro_student')
        drop procedure Pro_Student    
    go
    create procedure Pro_Student
    @Page tinyint = 20    --给定参数,输出age大于参数值的数据。可以设置默认值
    as 
    begin
        select * from Student where  S_age >= @Page
    end
    go

    exec Pro_Student @Page = 30

结果显示

修改存储过程的语句

    alter procedure Pro_Student
    @Page tinyint = 20   设置默认值
    @PSex nchar(1)
    as 
    begin
        select * from Student where  S_age >= @Page
    end
    go

    exec Pro_Student @Psex = '女'

结果显示

查询存储过程中的语句

exec sp_helptext Pro_Student;

三、使用时遇到的一些问题

在创建存储过程时,遇到”create procedure …”下画波浪线,提示”错误的语法:”create procedure”必须是批处理中仅有的语句”

原因及解决方法: “create procedure…” 之前有SQL语句,且没有”go”,加上”go”即可。

存储过程中,字段名或者表名为参数变量时的处理方法

create procedure 分页查询
    @numOfPage int = 5,
    @tableName nchar(20) = '',
    @tablePrimaryColumn nchar(50) = ''
as
declare @SQL nvarchar(2000)     --存储过程中字段名或者表名为变量,则需要将变量写入动态SQL中
set @SQL = 'select @a = count(*) from ' + @tableName;
declare @totalcount int 
        ,@times int = 1
exec sp_executesql @sql ,N'@a int output',@totalcount output    --exec(@sql)的动态SQL的值无法赋给变量,需要用到扩展存储过程

set @SQL='select top ' + convert(varchar(100),@numOfPage) + ' * from ' + @tableName     --动态SQL里面需将数值型转成字符型
exec(@SQL) 
while @times <= (@totalcount / @numOfPage)
begin
    set @sql = 'select top ' + convert(varchar(100),@numOfPage)  + '* from ' +  @tableName  +' where '+  @tablePrimaryColumn + 'not in (select top  ' + convert(varchar(100),@numOfPage * @times ) +  @tablePrimaryColumn + ' from ' + @tableName + ')'
    exec(@sql)
    set @times += 1
end
go

exec 分页查询 @numOfPage = 1000,@tableName = '表名',@tablePrimaryColumn = '列名'

说明：存储中遇到表名为参数变量时,SQL语句会报错,需要将SQL执行语句转换成动态语句,再用exec(@sql)来执行。
exec(@sql)得到的值,无法直接赋值给变量.因此这里使用了sp_executesql扩展存储过程。
上面的示例是实现分页查询的存储过程的示例。