/*====================================================
*描述：
存储过程知识点总结，以Northwind数据库的Employees表为例
======================================================*/
--========================1.没有参数的存储过程================
create procedure
usp_NoParameterSelect
as
begin
select * from dbo.Employees
end
GO
--========================执行测试========================
EXECUTE
usp_NoParameterSelect
GO
--=======================2.带参数的存储过程===================
create procedure usp_ParameterSelect
(
@employeeID INT
)
as
begin
select * from dbo.Employees
where EmployeeID =
@employeeID
end
--=======================执行测试===========================
EXECUTE
usp_ParameterSelect 1
GO
--=======================3.带多个参数的存储过程==================
create procedure usp_MultiParameterSelect
@employeeID
INT,
@employeeFirstName nvarchar(10)--要加上参数类型的长度
as
begin
select *
from dbo.Employees
where EmployeeID = @employeeID
and FirstName =
@employeeFirstName
end
--========================执行测试===========================
EXECUTE
usp_MultiParameterSelect 1,‘Nancy‘
GO
--=======================4.output输出存储过程====================
create
procedure usp_OutputSelect
@employeeID int ,
@employeeFirstName
nvarchar(10) output
as
begin
select @employeeFirstName = FirstName
from Employees
where EmployeeID = @employeeID
end
--=======================执行测试=============================
declare @name
nvarchar(10)
execute usp_OutputSelect 1,@name output
select @name as
name
GO
--======================5.return输出存储过程======================
create procedure usp_ReturnSelect
@employeeFirstName
nvarchar(10)
AS
begin
declare @employeeID INT
select @employeeID
= EmployeeID from Employees
where FirstName =
@employeeFirstName
return @employeeID --**返回值必须是INT类型**
end
GO
--======================执行测试===============================
declare
@employeeID INT
execute @employeeID = usp_ReturnSelect ‘Nancy‘
print
@employeeID
GO
--=====================6.同时有output和return输出的存储过程=============
create procedure usp_OutputAndReturnSelect
@firstName
nvarchar(10),
@lastName nvarchar(20) output
as
begin
declare
@employeeID INT;
select @employeeID = EmployeeID,@lastName=LastName from
Employees
where FirstName = @firstName
return @employeeID
end
GO
--======================执行测试================================
declare
@employeeID INT
declare @lastName nvarchar(20)
execute @employeeID =
usp_OutputAndReturnSelect ‘Nancy‘,@lastName output
select
@employeeID,@lastName
/*===========================================================
*****************************未完待续***************************************
*============================================================*/