SQL Server 存储过程

            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‘, ‘stud‘;
select*from stud;
--列重命名exec sp_rename ‘stud.name‘, ‘sName‘, ‘column‘;
exec sp_help ‘stud‘;
--重命名索引exec sp_rename N‘student.idx_cid‘, N‘idx_cidd‘, N‘index‘;
exec sp_help ‘student‘;

--查询所有存储过程select*from sys.objects where type =‘P‘;
select*from sys.objects where type_desc like‘%pro%‘and name like‘sp%‘;

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

            --
            创建存储过程
            if (exists (select*from sys.objects where name =‘proc_get_student‘))
    dropproc proc_get_student
gocreateproc proc_get_student
asselect*from student;

--调用、执行存储过程exec proc_get_student;

            --
            修改存储过程
            alter
            proc
             proc_get_student

            as
            select
            *
            from student;

            --
            带参存储过程
            if (object_id(‘proc_find_stu‘, ‘P‘) isnotnull)
    dropproc proc_find_stu
gocreateproc proc_find_stu(@startIdint, @endIdint)
asselect*from student where id between@startIdand@endIdgoexec proc_find_stu 2, 4;

            --
            带通配符参数存储过程
            if (object_id(‘proc_findStudentByName‘, ‘P‘) isnotnull)
    dropproc proc_findStudentByName
gocreateproc proc_findStudentByName(@namevarchar(20) =‘%j%‘, @nextNamevarchar(20) =‘%‘)
asselect*from student where name like@nameand name like@nextName;
goexec proc_findStudentByName;
exec proc_findStudentByName ‘%o%‘, ‘t%‘;

            if (object_id(‘proc_getStudentRecord‘, ‘P‘) isnotnull)
    dropproc proc_getStudentRecord
gocreateproc proc_getStudentRecord(
    @idint, --默认输入参数@namevarchar(20) out, --输出参数@agevarchar(20) output--输入输出参数)
asselect@name= name, @age= age  from student where id =@idand sex =@age;
go--declare@idint,
        @namevarchar(20),
        @tempvarchar(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‘) isnotnull)
    dropproc proc_temp
gocreateproc proc_temp
with recompile
asselect*from student;
goexec proc_temp;

            --
            加密WITH ENCRYPTION 
            if (object_id(‘proc_temp_encryption‘, ‘P‘) isnotnull)
    dropproc proc_temp_encryption
gocreateproc proc_temp_encryption
with encryption
asselect*from student;
goexec proc_temp_encryption;
exec sp_helptext ‘proc_temp‘;
exec sp_helptext ‘proc_temp_encryption‘;

            if (object_id(‘proc_cursor‘, ‘P‘) isnotnull)
    dropproc proc_cursor
gocreateproc proc_cursor
    @curcursorvarying output
asset@cur=cursor forward_only static forselect id, name, age from student;
    open@cur;
go--调用declare@exec_curcursor;
declare@idint,
        @namevarchar(20),
        @ageint;
exec proc_cursor @cur=@exec_cur output;--调用存储过程fetchnextfrom@exec_curinto@id, @name, @age;
while (@@fetch_status=0)
beginfetchnextfrom@exec_curinto@id, @name, @age;
    print‘id: ‘+convert(varchar, @id) +‘, name: ‘+@name+‘, age: ‘+convert(char, @age);
endclose@exec_cur;
deallocate@exec_cur;--删除游标

            --
            -存储过程、row_number完成分页
            if (object_id(‘pro_page‘, ‘P‘) isnotnull)
    dropproc proc_cursor
gocreateproc pro_page
    @startIndexint,
    @endIndexintasselectcount(*) from product
;    
    select*from (
        select row_number() over(orderby pid) as rowId, *from product 
    ) tempwheretemp.rowId between@startIndexand@endIndexgo--drop proc pro_pageexec pro_page 1, 4--
--分页存储过程if (object_id(‘pro_page‘, ‘P‘) isnotnull)
    dropproc pro_stu
gocreateprocedure pro_stu(
    @pageIndexint,
    @pageSizeint
)
asdeclare@startRowint, @endRowintset@startRow= (@pageIndex-1) *@pageSize+1set@endRow=@startRow+@pageSize-1select*from (
        select*, row_number() over (orderby id asc) asnumberfrom student 
    ) t
    where t.numberbetween@startRowand@endRow;

exec pro_stu 2, 2;

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

            raiserror(‘is error‘, 16, 1);
select*from sys.messages;
--使用sysmessages中定义的消息raiserror(33003, 16, 1);
raiserror(33006, 16, 1);