SQL之SQLServer存储过程简单样例(Updating)

--带参存储过程  
if (object_id('proc_find_stu', 'P') is not null)  
    drop proc proc_find_stu  
go  
create proc proc_find_stu(@startId int, @endId int)  
as  
    select * from student where id between @startId and @endId  
go  
  
exec proc_find_stu 2, 4;

--创建存储过程  
if (exists (select * from sys.objects where name = 'proc_get_student'))  
    drop proc proc_get_student  
go  
create proc proc_get_student  
as  
    select * from student;  
  
--调用、执行存储过程  
exec proc_get_student;

--带通配符参数存储过程  
if (object_id('proc_findStudentByName', 'P') is not null)  
    drop proc proc_findStudentByName  
go  
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')  
as  
    select * from student where name like @name and name like @nextName;  
go  
  
exec proc_findStudentByName;  
exec proc_findStudentByName '%o%', 't%';

-- 输出参数的存储过程
if (object_id('proc_getStudentRecord', 'P') is not null)  
    drop proc proc_getStudentRecord  
go  
create proc proc_getStudentRecord(  
    @id int, --默认输入参数  
    @name varchar(20) out, --输出参数  
    @age varchar(20) output--输入输出参数  
)  
as  
    select @name = name, @age = age  from student where id = @id and sex = @age;  
go  
  
--   
declare @id int,  
        @name varchar(20),  
        @temp varchar(20);  
set @id = 7;   
set @temp = 1;  
exec proc_getStudentRecord @id, @name out, @temp output;  
select @name, @temp;  
print @name + '#' + @temp;

-- 不缓存的存储过程
--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 student;  
go  
  
exec proc_temp;

--加密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 student;  
go  
  
exec proc_temp_encryption;  
exec sp_helptext 'proc_temp';  
exec sp_helptext 'proc_temp_encryption';

在前面介绍游标的例子中，SELECT语句都没有WHERE子句，或者用WHERE子句指定了一个固定的条件，这样每次都查询同样的数据。在更多的情况下，可能要根据实际情况查询不同的数据。为了通过游标对数据进行更加灵活的处理，可以为游标定义参数，这些参数可以用在WHERE子句中。在打开游标时，指定实际的参数值，这样游标在每次打开时，可以根据不同的实际参数值，返回所需的不同数据。

-- 带游标参数的存储过程
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 student;  
    open @cur;  
go  
--调用  
declare @exec_cur cursor;  
declare @id int,  
        @name varchar(20),  
        @age int;  
exec proc_cursor @cur = @exec_cur output;--调用存储过程  
fetch next from @exec_cur into @id, @name, @age;  
while (@@fetch_status = 0)  
begin  
    fetch next from @exec_cur into @id, @name, @age;  
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);  
end  
close @exec_cur;  
deallocate @exec_cur;--删除游标

---存储过程、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 product  
;      
    select * from (  
        select row_number() over(order by pid) as rowId, * from product   
    ) temp  
    where temp.rowId between @startIndex and @endIndex  
go  
drop proc pro_page  
exec pro_page 1, 4  
--  
--分页存储过程  
if (object_id('pro_page', 'P') is not null)  
    drop proc pro_stu  
go  
create procedure pro_stu(  
    @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 student   
    ) t  
    where t.number between @startRow and @endRow;  
  
exec pro_stu 2, 2;

SQL Server 修改存储过程
如果需要修改现有的存储过程，只需更换掉 CREATE ，使用 ALTER。
我们在 “Latest” 和 “Tasks”间添加一个空格（即“Latest Tasks”），并添加描述字段，如下

ALTER PROCEDURE LatestTasks @Count int 
AS 
SET ROWCOUNT @Count 
SELECT TaskName AS "Latest Tasks", Description, DateCreated FROM Tasks 
ORDER BY DateCreated DESC