存储过程知识总结【一】

/*====================================================*描述： 存储过程知识点总结，以Northwind数据库的Employees表为例======================================================*/

--========================1.没有参数的存储过程================create PRocedure usp_NoParameterSelectasbeginselect * from dbo.Employeesend

GO

--========================执行测试========================EXECUTE usp_NoParameterSelect

GO--=======================2.带参数的存储过程===================

create procedure usp_ParameterSelect(@employeeID INT)as

beginselect * from dbo.Employeeswhere EmployeeID = @employeeIDend

--=======================执行测试===========================EXECUTE usp_ParameterSelect 1

GO--=======================3.带多个参数的存储过程==================

create procedure usp_MultiparameterSelect@employeeID INT,@employeeFirstName nvarchar(10)--要加上参数类型的长度asbeginselect * from dbo.Employeeswhere EmployeeID = @employeeIDand FirstName = @employeeFirstNameend

--========================执行测试===========================EXECUTE usp_MultiParameterSelect 1,'Nancy'

GO

--=======================4.output输出存储过程====================create procedure usp_OutputSelect@employeeID int ,@employeeFirstName nvarchar(10) outputasbeginselect @employeeFirstName = FirstName from Employeeswhere EmployeeID = @employeeIDend

--=======================执行测试=============================declare @name nvarchar(10)execute usp_OutputSelect 1,@name outputselect @name as nameGO

--======================5.return输出存储过程======================

create procedure usp_ReturnSelect@employeeFirstName nvarchar(10)ASbegin declare @employeeID INTselect @employeeID = EmployeeID from Employeeswhere FirstName = @employeeFirstNamereturn @employeeID--**返回值必须是INT类型**end

GO

--======================执行测试===============================declare @employeeID INTexecute @employeeID = usp_ReturnSelect 'Nancy'print @employeeID

GO

--=====================6.同时有output和return输出的存储过程=============

create procedure usp_OutputAndReturnSelect@firstName nvarchar(10),@lastName nvarchar(20) outputasbegindeclare @employeeID INT;select @employeeID = EmployeeID,@lastName=LastName from Employeeswhere FirstName = @firstNamereturn @employeeIDend

GO

--======================执行测试================================declare @employeeID INTdeclare @lastName nvarchar(20)execute @employeeID = usp_OutputAndReturnSelect 'Nancy',@lastName outputselect @employeeID,@lastName

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