首页 > 其他 > 详细

存储过程知识总结【一】

时间:2014-01-14 19:50:26      阅读:459      评论:0      收藏:0      [点我收藏+]

/*====================================================
*描述:            存储过程知识点总结,以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


/*===========================================================
*****************************未完待续***************************************
*============================================================*/

存储过程知识总结【一】

原文:http://www.cnblogs.com/ucos/p/3512965.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!