当前位置：Gxlcms > 数据库问题 > SQL Server 存储过程

SQL Server 存储过程

时间：2021-07-01 10:21:17 帮助过：2人阅读

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;

    系统存储过程示例：

  1. <span style="color: #008080;">--</span><span style="color: #008080;">表重命名</span>
  2. <span style="color: #0000ff;">exec</span> sp_rename <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">stu</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">stud</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> stud;
  4. </span><span style="color: #008080;">--</span><span style="color: #008080;">列重命名</span>
  5. <span style="color: #0000ff;">exec</span> sp_rename <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">stud.name</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sName</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">column</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  6. </span><span style="color: #0000ff;">exec</span> sp_help <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">stud</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  7. </span><span style="color: #008080;">--</span><span style="color: #008080;">重命名索引</span>
  8. <span style="color: #0000ff;">exec</span> sp_rename N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">student.idx_cid</span><span style="color: #ff0000;">‘</span>, N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">idx_cidd</span><span style="color: #ff0000;">‘</span>, N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">index</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  9. </span><span style="color: #0000ff;">exec</span> sp_help <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">student</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  10. </span><span style="color: #008080;">--</span><span style="color: #008080;">查询所有存储过程</span>
  11. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> type <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  12. </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> type_desc <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%pro%</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">and</span> name <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sp%</span><span style="color: #ff0000;">‘</span>;

 

Ø 用户自定义存储过程 

   1、 创建语法 

  1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">procedure</span><span style="color: #000000;"> pro_name
  2.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">{@参数数据类型} [=默认值</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">output</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  3.      {</span><span style="color: #008000;">@参数数据类型</span>} <span style="color: #ff0000;">[</span><span style="color: #ff0000;">=默认值</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">output</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  4.      ....
  5.     ]
  6. </span><span style="color: #0000ff;">as</span><span style="color: #000000;">
  7.     SQL_statements</span>

 

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

  1. <span style="color: #008080;">--</span><span style="color: #008080;">创建存储过程</span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #808080;">exists</span> (<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sys.objects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_get_student</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_get_student
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_get_student
  6. </span><span style="color: #0000ff;">as</span>
  7.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;
  8. </span><span style="color: #008080;">--</span><span style="color: #008080;">调用、执行存储过程</span>
  9. <span style="color: #0000ff;">exec</span> proc_get_student;
  1.  

   3、 修改存储过程 

  1. <span style="color: #008080;">--</span><span style="color: #008080;">修改存储过程</span>
  2. <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_get_student
  3. </span><span style="color: #0000ff;">as</span>
  4. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student;
  1.  

   4、 带参存储过程 

  1. <span style="color: #008080;">--</span><span style="color: #008080;">带参存储过程</span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_find_stu</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_find_stu
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> proc_find_stu(<span style="color: #008000;">@startId</span> <span style="color: #0000ff;">int</span>, <span style="color: #008000;">@endId</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">)
  6. </span><span style="color: #0000ff;">as</span>
  7.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> id <span style="color: #808080;">between</span> <span style="color: #008000;">@startId</span> <span style="color: #808080;">and</span> <span style="color: #008000;">@endId</span>
  8. <span style="color: #0000ff;">go</span>
  9. <span style="color: #0000ff;">exec</span> proc_find_stu <span style="color: #800000; font-weight: bold;">2</span>, <span style="color: #800000; font-weight: bold;">4</span>;
  1.  

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

  1. <span style="color: #008080;">--</span><span style="color: #008080;">带通配符参数存储过程</span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_findStudentByName</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_findStudentByName
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span> proc_findStudentByName(<span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%j%</span><span style="color: #ff0000;">‘</span>, <span style="color: #008000;">@nextName</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  6. </span><span style="color: #0000ff;">as</span>
  7.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span> <span style="color: #008000;">@name</span> <span style="color: #808080;">and</span> name <span style="color: #808080;">like</span> <span style="color: #008000;">@nextName</span><span style="color: #000000;">;
  8. </span><span style="color: #0000ff;">go</span>
  9. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> proc_findStudentByName;
  10. </span><span style="color: #0000ff;">exec</span> proc_findStudentByName <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%o%</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">t%</span><span style="color: #ff0000;">‘</span>;
  1.  

   6、 带输出参数存储过程 

  1. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_getStudentRecord</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  2.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_getStudentRecord
  3. </span><span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_getStudentRecord(
  5.     </span><span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span>, <span style="color: #008080;">--</span><span style="color: #008080;">默认输入参数</span>
  6.     <span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) out, <span style="color: #008080;">--</span><span style="color: #008080;">输出参数</span>
  7.     <span style="color: #008000;">@age</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) output<span style="color: #008080;">--</span><span style="color: #008080;">输入输出参数</span>
  8. <span style="color: #000000;">)
  9. </span><span style="color: #0000ff;">as</span>
  10.     <span style="color: #0000ff;">select</span> <span style="color: #008000;">@name</span> <span style="color: #808080;">=</span> name, <span style="color: #008000;">@age</span> <span style="color: #808080;">=</span> age  <span style="color: #0000ff;">from</span> student <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #008000;">@id</span> <span style="color: #808080;">and</span> sex <span style="color: #808080;">=</span> <span style="color: #008000;">@age</span><span style="color: #000000;">;
  11. </span><span style="color: #0000ff;">go</span>
  12. <span style="color: #008080;">--</span> 
  13. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">,
  14.         </span><span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
  15.         </span><span style="color: #008000;">@temp</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">);
  16. </span><span style="color: #0000ff;">set</span> <span style="color: #008000;">@id</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;">; 
  17. </span><span style="color: #0000ff;">set</span> <span style="color: #008000;">@temp</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  18. </span><span style="color: #0000ff;">exec</span> proc_getStudentRecord <span style="color: #008000;">@id</span>, <span style="color: #008000;">@name</span> out, <span style="color: #008000;">@temp</span><span style="color: #000000;"> output;
  19. </span><span style="color: #0000ff;">select</span> <span style="color: #008000;">@name</span>, <span style="color: #008000;">@temp</span><span style="color: #000000;">;
  20. </span><span style="color: #0000ff;">print</span> <span style="color: #008000;">@name</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">#</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@temp</span>;

 

   7、 不缓存存储过程 

  1. <span style="color: #008080;">--</span><span style="color: #008080;">WITH RECOMPILE 不缓存</span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_temp</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_temp
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_temp
  6. </span><span style="color: #0000ff;">with</span><span style="color: #000000;"> recompile
  7. </span><span style="color: #0000ff;">as</span>
  8.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;
  9. </span><span style="color: #0000ff;">go</span>
  10. <span style="color: #0000ff;">exec</span> proc_temp;
  1.  

   8、 加密存储过程 

  1. <span style="color: #008080;">--</span><span style="color: #008080;">加密WITH ENCRYPTION </span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_temp_encryption</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_temp_encryption
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_temp_encryption
  6. </span><span style="color: #0000ff;">with</span><span style="color: #000000;"> encryption
  7. </span><span style="color: #0000ff;">as</span>
  8.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;
  9. </span><span style="color: #0000ff;">go</span>
  10. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> proc_temp_encryption;
  11. </span><span style="color: #0000ff;">exec</span> sp_helptext <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_temp</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  12. </span><span style="color: #0000ff;">exec</span> sp_helptext <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_temp_encryption</span><span style="color: #ff0000;">‘</span>;
  1.  

   9、 带游标参数存储过程 

  1. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">proc_cursor</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  2.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_cursor
  3. </span><span style="color: #0000ff;">go</span>
  4. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_cursor
  5.     </span><span style="color: #008000;">@cur</span> <span style="color: #0000ff;">cursor</span> <span style="color: #0000ff;">varying</span><span style="color: #000000;"> output
  6. </span><span style="color: #0000ff;">as</span>
  7.     <span style="color: #0000ff;">set</span> <span style="color: #008000;">@cur</span> <span style="color: #808080;">=</span> <span style="color: #0000ff;">cursor</span> forward_only static <span style="color: #0000ff;">for</span>
  8.     <span style="color: #0000ff;">select</span> id, name, age <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;
  9.     </span><span style="color: #0000ff;">open</span> <span style="color: #008000;">@cur</span><span style="color: #000000;">;
  10. </span><span style="color: #0000ff;">go</span>
  11. <span style="color: #008080;">--</span><span style="color: #008080;">调用</span>
  12. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@exec_cur</span> <span style="color: #0000ff;">cursor</span><span style="color: #000000;">;
  13. </span><span style="color: #0000ff;">declare</span> <span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">,
  14.         </span><span style="color: #008000;">@name</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
  15.         </span><span style="color: #008000;">@age</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">;
  16. </span><span style="color: #0000ff;">exec</span> proc_cursor <span style="color: #008000;">@cur</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@exec_cur</span> output;<span style="color: #008080;">--</span><span style="color: #008080;">调用存储过程</span>
  17. <span style="color: #0000ff;">fetch</span> <span style="color: #0000ff;">next</span> <span style="color: #0000ff;">from</span> <span style="color: #008000;">@exec_cur</span> <span style="color: #0000ff;">into</span> <span style="color: #008000;">@id</span>, <span style="color: #008000;">@name</span>, <span style="color: #008000;">@age</span><span style="color: #000000;">;
  18. </span><span style="color: #0000ff;">while</span> (<span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">)
  19. </span><span style="color: #0000ff;">begin</span>
  20.     <span style="color: #0000ff;">fetch</span> <span style="color: #0000ff;">next</span> <span style="color: #0000ff;">from</span> <span style="color: #008000;">@exec_cur</span> <span style="color: #0000ff;">into</span> <span style="color: #008000;">@id</span>, <span style="color: #008000;">@name</span>, <span style="color: #008000;">@age</span><span style="color: #000000;">;
  21.     </span><span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">id: </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>, <span style="color: #008000;">@id</span>) <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">, name: </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@name</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">, age: </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">char</span>, <span style="color: #008000;">@age</span><span style="color: #000000;">);
  22. </span><span style="color: #0000ff;">end</span>
  23. <span style="color: #0000ff;">close</span> <span style="color: #008000;">@exec_cur</span><span style="color: #000000;">;
  24. </span><span style="color: #0000ff;">deallocate</span> <span style="color: #008000;">@exec_cur</span>;<span style="color: #008080;">--</span><span style="color: #008080;">删除游标</span>

 

   10、 分页存储过程 

  1. <span style="color: #008080;">--</span><span style="color: #008080;">-存储过程、row_number完成分页</span>
  2. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">pro_page</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  3.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> proc_cursor
  4. </span><span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> pro_page
  6.     </span><span style="color: #008000;">@startIndex</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">,
  7.     </span><span style="color: #008000;">@endIndex</span> <span style="color: #0000ff;">int</span>
  8. <span style="color: #0000ff;">as</span>
  9.     <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">from</span><span style="color: #000000;"> product
  10. ;    
  11.     </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> (
  12.         </span><span style="color: #0000ff;">select</span> row_number() <span style="color: #0000ff;">over</span>(<span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> pid) <span style="color: #0000ff;">as</span> rowId, <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> product 
  13.     ) </span><span style="color: #0000ff;">temp</span>
  14.     <span style="color: #0000ff;">where</span> <span style="color: #0000ff;">temp</span>.rowId <span style="color: #808080;">between</span> <span style="color: #008000;">@startIndex</span> <span style="color: #808080;">and</span> <span style="color: #008000;">@endIndex</span>
  15. <span style="color: #0000ff;">go</span>
  16. <span style="color: #008080;">--</span><span style="color: #008080;">drop proc pro_page</span>
  17. <span style="color: #0000ff;">exec</span> pro_page <span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #800000; font-weight: bold;">4</span>
  18. <span style="color: #008080;">--
  19. --</span><span style="color: #008080;">分页存储过程</span>
  20. <span style="color: #0000ff;">if</span> (<span style="color: #ff00ff;">object_id</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">pro_page</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">is</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  21.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> pro_stu
  22. </span><span style="color: #0000ff;">go</span>
  23. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">procedure</span><span style="color: #000000;"> pro_stu(
  24.     </span><span style="color: #008000;">@pageIndex</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">,
  25.     </span><span style="color: #008000;">@pageSize</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">
  26. )
  27. </span><span style="color: #0000ff;">as</span>
  28.     <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@startRow</span> <span style="color: #0000ff;">int</span>, <span style="color: #008000;">@endRow</span> <span style="color: #0000ff;">int</span>
  29.     <span style="color: #0000ff;">set</span> <span style="color: #008000;">@startRow</span> <span style="color: #808080;">=</span> (<span style="color: #008000;">@pageIndex</span> <span style="color: #808080;">-</span> <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">*</span> <span style="color: #008000;">@pageSize</span> <span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span>
  30.     <span style="color: #0000ff;">set</span> <span style="color: #008000;">@endRow</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@startRow</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@pageSize</span> <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>
  31.     <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> (
  32.         </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span>, row_number() <span style="color: #0000ff;">over</span> (<span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> id <span style="color: #0000ff;">asc</span>) <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">number</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student 
  33.     ) t
  34.     </span><span style="color: #0000ff;">where</span> t.<span style="color: #0000ff;">number</span> <span style="color: #808080;">between</span> <span style="color: #008000;">@startRow</span> <span style="color: #808080;">and</span> <span style="color: #008000;">@endRow</span><span style="color: #000000;">;
  35. </span><span style="color: #0000ff;">exec</span> pro_stu <span style="color: #800000; font-weight: bold;">2</span>, <span style="color: #800000; font-weight: bold;">2</span>;

 

Ø Raiserror 

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

   语法如下： 

  1. <span style="color: #0000ff;">Raiserror</span>({msg_id <span style="color: #808080;">|</span> msg_str <span style="color: #808080;">|</span> <span style="color: #008000;">@local_variable</span><span style="color: #000000;">}
  2.   {, severity, state}
  3.   </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">,argument[,…n</span><span style="color: #ff0000;">]</span><span style="color: #000000;">]
  4.   </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">with option[,…n</span><span style="color: #ff0000;">]</span><span style="color: #000000;">]
  5. )</span>
  1.  

   # 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。

  1. <span style="color: #0000ff;">raiserror</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">is error</span><span style="color: #ff0000;">‘</span>, <span style="color: #800000; font-weight: bold;">16</span>, <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
  2. </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> sys.messages;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;">使用sysmessages中定义的消息</span>
  4. <span style="color: #0000ff;">raiserror</span>(<span style="color: #800000; font-weight: bold;">33003</span>, <span style="color: #800000; font-weight: bold;">16</span>, <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
  5. </span><span style="color: #0000ff;">raiserror</span>(<span style="color: #800000; font-weight: bold;">33006</span>, <span style="color: #800000; font-weight: bold;">16</span>, <span style="color: #800000; font-weight: bold;">1</span>);
  1. <span style="color: #0000ff;"><span style="color: #006080;"><span style="color: #0000ff;"><span style="color: #0000ff;"><span style="color: #0000ff;"><span style="color: #0000ff;">转自：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html</span></span></span></span></span></span>

SQL Server 存储过程

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！