/*====================================================*描述： 存儲過程知識點總結，以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

/*===========================================================*****************************未完待續****************************************============================================================*/