SqlServer存储过程

发布时间：2020-12-12 16:05:52 所属栏目：MsSql教程来源：网络整理

导读：?常用系统存储过程有： exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext 'sp_stor

?常用系统存储过程有：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student,stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB,myDB;--更改数据库名称
exec sp_defaultdb 'master','myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

??? 系统存储过程示例： --表重命名 exec sp_rename 'stu',128)">'stud'; select * from stud; --列重命名 'stud.name',128)">'sName',128)">'column'; exec sp_help 'stud'; --重命名索引 exec sp_rename N'student.idx_cid',N'idx_cidd',128)">'index'; 'student'; --查询所有存储过程 from sys.objects where type = 'P'; where type_desc like '%pro%' and name 'sp%';

? 用户自定义存储过程

?? 1、创建语法

create proc | procedure pro_name [{@参数数据类型} [=默认值] [output],{@参数数据类型} [=默认值] [as SQL_statements

?? 2、创建不带参数存储过程

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

?? 3、修改存储过程

--修改存储过程 alter as from student;

?? 4、带参存储过程

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

?? 5、带通配符参数存储过程

--带通配符参数存储过程 'proc_findStudentByName',255)">proc proc_findStudentByName proc proc_findStudentByName(@name varchar(20) = '%j%',@nextName '%') where name like @name like @nextName; exec proc_findStudentByName; exec proc_findStudentByName '%o%',128)">'t%';

?? 6、带输出参数存储过程

'proc_getStudentRecord',255)">proc proc_getStudentRecord proc proc_getStudentRecord( @id varchar(20) out,--输出参数 @age output--输入输出参数 ) select @name = name,@age = age where id = @id and sex = @age; -- declare @id varchar(20),@temp varchar(20); set @id = 7; set @temp = 1; exec proc_getStudentRecord @id,255)">output; select @name,@temp; print @name + '#' + @temp;

?? 7、不缓存存储过程

--WITH RECOMPILE 不缓存 'proc_temp',255)">proc proc_temp with recompile from student; exec proc_temp;

?? 8、加密存储过程

--加密WITH ENCRYPTION 'proc_temp_encryption',255)">proc proc_temp_encryption with encryption exec proc_temp_encryption; 'proc_temp'; 'proc_temp_encryption';

?? 9、带游标参数存储过程

'proc_cursor',255)">proc proc_cursor proc proc_cursor @cur cursor varying output set @cur = cursor forward_only static for select id,name,age from student; open @cur; go --调用 declare @exec_cur cursor; int; exec proc_cursor @cur = @exec_cur output;--调用存储过程 fetch next from @exec_cur into @id,@name,@age; while (@@fetch_status = 0) begin print 'id: ' + convert(varchar,@id) + ',name: ' + @name + char,@age); end close @exec_cur; deallocate @exec_cur;--删除游标
?? 10、分页存储过程

---存储过程、row_number完成分页 'pro_page',255)">proc pro_page @startIndex int select count(*) from product ; from ( select row_number() over(order by pid) as rowId,* from product ) temp where temp.rowId between @startIndex and @endIndex go --proc pro_page exec pro_page 1,4 -- --分页存储过程 proc pro_stu procedure pro_stu( @pageIndex int ) declare @startRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select *,row_number() over (by id asc) as number from student ) t where t.number between @startRow and @endRow; exec pro_stu 2,2;
? Raiserror

Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。

?? 语法如下：

Raiserror({msg_id | msg_str | @local_variable} {,severity,255)">state} [,argument[,…n]] [with option[,…n]] )

?? # msg_id:在sysmessages系统表中指定的用户定义错误信息

?? # msg_str:用户定义的信息，信息最大长度在2047个字符。

?? # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。

??? 任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。

?? # state：介于1至127直接的任何整数。State默认值是1。

raiserror('is error',16,1); from sys.messages; --使用sysmessages中定义的消息 raiserror(33003,255)">raiserror(33006,1);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!