图解SQL Server 存储过程教程一

    以下给出存储过程代码和鄙人运行成功的截图。把以下存储过程代码中的tablename替换为自己的表名。

一 不带参数存储过程

if (exists (select * from sys.objects where name = 'proc_get_tablename'))
    drop proc proc_get_tablename
go
create proc proc_get_tablename
as
    select * from tablename;

二 带参存储过程

if (object_id('proc_find_tablename', 'P') is not null)
    drop proc proc_find_tablename
go
create proc proc_find_tablename(@startId int, @endId int)
as
    select * from tablename where id between @startId and @endId
go

三 带通配符参数存储过程

if (object_id('proc_findtablenameByName', 'P') is not null)
    drop proc proc_findtablenameByName
go
create proc proc_findtablenameByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from tablename where name like @name and name like @nextName;
go

四 带输出参数存储过程
if (object_id('proc_gettablenameRecord', 'P') is not null)
    drop proc proc_gettablenameRecord
go
create proc proc_gettablenameRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from tablename where id = @id and sex = @age;
go

五 不缓存存储过程
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from tablename;
go

六 加密存储过程****
--加密WITH ENCRYPTION 
if (object_id('proc_temp_encryption', 'P') is not null)
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from tablename;
go

    可以看到加密存储过程显示的图标与别的不同，有一个小锁的标识；

七 带游标参数存储过程
if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from tablename;
    open @cur;
go

如果出现 必须声明标量变量 "@cur" 错误；
可试着加入SET NOCOUNT ON


编写如下的执行语句，然后执行，
USE dboa;
GO
DECLARE @MyCursor CURSOR;
exec dbo.proc_cursor @cur=@MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

结果如下图，一行一行的提取了行；

关于 须声明标量变量 "@cur" 错误，网友有言：
变量的使用必须和定义在一起
1.declare @name varchar(30) = 'aaaa'
print @name
2.declare @name varchar(30) = 'aaaa'
go
print @name
1可以执行，而2由于声明与使用分开，就会报错

八分页存储过程1

---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from tablename
;    
    select * from (
        select row_number() over(order by pid) as rowId, * from tablename 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go

    执行前提示输入参数，此处每页5条；当然实际需要分页的情况都是记录数很多；例如鄙人曾做过10万记录每页分1万的情况；

九 分页存储过程2
if (object_id('pro_page', 'P') is not null)
    drop proc pro_tablename
go
create procedure pro_tablename(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from tablename 
    ) t
    where t.number between @startRow and @endRow;

    输入参数是起始行号，页尺寸；

示例数据库 dboa 下载：

http://pan.baidu.com/s/1bntWzSV

全部存储过程代码下载：

http://pan.baidu.com/s/1o6kgAP8