当前位置：Gxlcms > 数据库问题 > SqlServer存储过程基础

SqlServer存储过程基础

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

USE Northwind; 2 GO 3 4 IF exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[sp_name]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘)= 1) 5 BEGIN --判断数据库中是否已经存在该存储过程 6 DROP PROCEDURE sp_name; 7 END 8 GO 9 CREATE PROCEDURE sp_name 10 AS 11 BEGIN 12 SELECT * FROM Employees 13 END 14 EXEC sp_name; View Code

二.带输入参数

<span style="color: #008080;"> 1</span> <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> dbo.sysobjects <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span><span style="color: #ff00ff;">object_id</span>(N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[dbo].[sp_Employees_GetById]</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">and</span> <span style="color: #ff00ff;">OBJECTPROPERTY</span>(id, N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IsProcedure</span><span style="color: #ff0000;">‘</span>)<span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
</span><span style="color: #008080;"> 2</span> <span style="color: #0000ff;">BEGIN</span>  <span style="color: #008080;">/*</span><span style="color: #008080;">判断数据库中是否已经存在该存储过程</span><span style="color: #008080;">*/</span>
<span style="color: #008080;"> 3</span>   <span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"> sp_Employees_GetById;
</span><span style="color: #008080;"> 4</span> <span style="color: #0000ff;">END</span>
<span style="color: #008080;"> 5</span> <span style="color: #0000ff;">GO</span>
<span style="color: #008080;"> 6</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"> sp_Employees_GetById
</span><span style="color: #008080;"> 7</span> <span style="color: #000000;">(
</span><span style="color: #008080;"> 8</span> <span style="color: #008000;">@LastName</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
</span><span style="color: #008080;"> 9</span> <span style="color: #008000;">@FirstName</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">)
</span><span style="color: #008080;">10</span> <span style="color: #000000;">)
</span><span style="color: #008080;">11</span> <span style="color: #0000ff;">AS</span>
<span style="color: #008080;">12</span> <span style="color: #0000ff;">BEGIN</span>
<span style="color: #008080;">13</span>   <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> Employees e <span style="color: #0000ff;">WHERE</span> e.FirstName <span style="color: #808080;">=</span> <span style="color: #008000;">@FirstName</span> <span style="color: #808080;">AND</span> e.LastName <span style="color: #808080;">=</span> <span style="color: #008000;">@LastName</span><span style="color: #000000;">;
</span><span style="color: #008080;">14</span> <span style="color: #0000ff;">END</span>
<span style="color: #008080;">15</span> <span style="color: #008080;">--</span><span style="color: #008080;">执行</span>
<span style="color: #008080;">16</span> <span style="color: #0000ff;">EXEC</span> sp_Employees_GetById <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Davolio</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Nancy</span><span style="color: #ff0000;">‘</span>;

View Code

SqlServer存储过程基础

标签：

SqlServer存储过程基础

人气教程排行