SqlServer存储过程基础

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

二.带输入参数

 1 IF exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[sp_Employees_GetById]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘)= 1)
 2 BEGIN  /*判断数据库中是否已经存在该存储过程*/
 3   DROP PROCEDURE sp_Employees_GetById;
 4 END
 5 GO
 6 CREATE PROCEDURE sp_Employees_GetById
 7 (
 8 @LastName varchar(20),
 9 @FirstName varchar(20)
10 )
11 AS
12 BEGIN
13   SELECT TOP 3 * FROM Employees e WHERE e.FirstName = @FirstName AND e.LastName = @LastName;
14 END
15 --执行
16 EXEC sp_Employees_GetById ‘Davolio‘,‘Nancy‘;

View Code

 

SqlServer存储过程基础

标签：