SQL Server之十大存储过程

下面介绍十大不同类型存储过程。

用户自定义存储过程  1、 创建语法 create proc | procedure pro_name  [{@参数数据类型} [=默认值] [output],   {@参数数据类型} [=默认值] [output],   ....  ]as  SQL_statements   2、 创建不带参数存储过程 --创建存储过程if (exists (select * from sys.objects where name = 'proc_get_student'))  drop proc proc_get_studentgocreate proc proc_get_studentas  select * from student;--调用、执行存储过程exec proc_get_student;  3、 修改存储过程 --修改存储过程alter proc proc_get_studentasselect * from student;  4、 带参存储过程 --带参存储过程if (object_id('proc_find_stu', 'P') is not null)  drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as  select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;  5、 带通配符参数存储过程 --带通配符参数存储过程if (object_id('proc_findStudentByName', 'P') is not null)  drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as  select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';  6、 带输出参数存储过程 if (object_id('proc_getStudentRecord', 'P') is not null)  drop proc proc_getStudentRecordgocreate 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;  7、 不缓存存储过程 --WITH RECOMPILE 不缓存if (object_id('proc_temp', 'P') is not null)  drop proc proc_tempgocreate proc proc_tempwith recompileas  select * from student;goexec proc_temp;  8、 加密存储过程 --加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null)  drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas  select * from student;goexec proc_temp_encryption;exec sp_helptext 'proc_temp';exec sp_helptext 'proc_temp_encryption';  9、 带游标参数存储过程 if (object_id('proc_cursor', 'P') is not null)  drop proc proc_cursorgocreate proc proc_cursor  @cur cursor varying outputas  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);endclose @exec_cur;deallocate @exec_cur;--删除游标  10、 分页存储过程 ---存储过程、row_number完成分页if (object_id('pro_page', 'P') is not null)  drop proc proc_cursorgocreate proc pro_page  @startIndex int,  @endIndex intas  select count(*) from product;    select * from (    select row_number() over(order by pid) as rowId, * from product   ) temp  where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分页存储过程if (object_id('pro_page', 'P') is not null)  drop proc pro_stugocreate 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;

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：SQL Server之十大存储过程

关键词：sql