--查看数据库exec sp_databases ;--查看表exec sp_tables ;--查看列exec sp_columns WMS_ASN;--查看索引exec sp_helpindex WMS_ASN;--约束exec sp_helpconstraint WMS_ASN;--列出数据库中的所有存储过程exec sp_stored_procedures--查看存储过程创建、定义语句exec sp_helptext 'xp_revokelogin'--修改表、索引、列的名称exec sp_rename WMS_ASN,WMS_ASNSexec sp_rename 'wms_asn.kids','KID','column'select * from wms_asn
--更改数据库名称exec sp_renamedb myTempDB, myDB;--更改登录名的默认数据库exec sp_defaultdb 'master', 'myDB';--数据库帮助，查询数据库信息exec sp_helpdbexec sp_helpdb master
--重命名索引exec sp_rename N'WMS_ASN.PK_WMS_ASN_KID1', N'PK_WMS_ASN_KID', N'index';exec sp_help 'WMS_ASN';
--查询所有存储过程select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
Ø 用户自定义存储过程
1. 创建语法
create proc | procedure pro_name [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ]as SQL_statements
2.创建不带参数存储过程
--创建存储过程if (exists (select * from sys.objects where name = 'proc_get_asn')) drop proc proc_get_asngocreate proc proc_get_asnas select * from wms_asn;
--调用、执行存储过程exec proc_get_asn;
3.修改存储过程
--修改存储过程alter proc proc_get_asnasselect * from wms_asn where type='0003' ;drop proc proc_get_asn4. 带参存储过程
--带参存储过程if (object_id('proc_find_asn', 'P') is not null) drop proc proc_find_asngocreate proc proc_find_asn(@startId numeric, @endId numeric)as select * from wms_asn where gross_weight between @startId and @endIdgo
exec proc_find_asn 500, 1000;
exec proc_find_asn 0,10000;
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;go
exec 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;go
exec 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;go
exec 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;--删除游标
转载于:https://www.cnblogs.com/teamate/p/3718827.html
