MSSQL创建存储过程

--创建无参存储过程

--检测是否存在要创建的存储过程，如果存在将其删除
IF EXISTS(SELECT * FROM sysobjects WHERE name='StaffInfoSel')
DROP PROC StaffInfoSel
--删除之后要加入批处理的GO
GO
CREATE PROCEDURE StaffInfoSel
AS
    SELECT StaffNum,StaffName,StaffPhone,StaffDepartment.StaffDepartName,StaffRemark,CASE StaffInfo.StaffState WHEN 'True' THEN '启用' WHEN 'False' THEN '停用' END newStaffState,CASE StaffInfo.IsLeader WHEN 'True' THEN '是' WHEN 'False' THEN '否' END newIsLeader,CASE StaffInfo.IsAdmin WHEN 'True' THEN '是' WHEN 'False' THEN '否' END newIsAdmin FROM StaffInfo LEFT JOIN StaffDepartment ON StaffInfo.StaffDepartID = StaffDepartment.StaffDepartID
GO

--创建有参

--检测是否存在要创建的存储过程，如果存在将其删除
IF EXISTS(SELECT * FROM sysobjects WHERE name='StaffInfoSel')
DROP PROC StaffInfoSel
--删除之后要加入批处理的GO
GO
CREATE PROCEDURE StaffInfoSel
 @hero nvarchar(20) = NULL
AS
IF @hero IS NULL
BEGIN

END
    SELECT StaffNum,StaffName,StaffPhone,StaffDepartment.StaffDepartName,StaffRemark,CASE StaffInfo.StaffState WHEN 'True' THEN '启用' WHEN 'False' THEN '停用' END newStaffState,CASE StaffInfo.IsLeader WHEN 'True' THEN '是' WHEN 'False' THEN '否' END newIsLeader,CASE StaffInfo.IsAdmin WHEN 'True' THEN '是' WHEN 'False' THEN '否' END newIsAdmin FROM StaffInfo LEFT JOIN StaffDepartment ON StaffInfo.StaffDepartID = StaffDepartment.StaffDepartID
GO