use DB_SMS
go

--1. 創建存儲過程，實現月，日，小時，分鐘，秒的雙位顯示
if exists (select 1 from sysobjects where name = 'proc_formate')
	drop procedure proc_formate
go
create procedure proc_formate
	@srcNum varchar(2),			 --要格式化的數
	@destNum varchar(2) output	 --格式化后的數
as
	if cast(@srcNum as int) < 10
		set @destNum = '0'+ @srcNum
	else 
		set @destNum = @srcNum
go

--2. 創建存儲過程，實現編號的自動生成
if exists (select 1 from sysobjects where name = 'proc_createID')
	drop procedure proc_createID
go
create procedure proc_createID
	@prefix varchar(3),		--編號前綴
	@id varchar(17) output	--生成的編號	
as
	--存放生成的編號
	--declare @number varchar(15)	
	--獲取當前的時間
	declare @year varchar(4)	
	set @year = datepart(year, getdate())
	declare @month varchar(2)
	set @month = datepart(month, getdate())
	declare @day varchar(2)
	set @day = datepart(day, getdate())
	declare @hour varchar(2)
	set @hour = datepart(hour, getdate())
	declare @minute varchar(2)
	set @minute = datepart(minute, getdate())
	declare @second varchar(2)
	set @second = datepart(second, getdate())
	--格式化當前時間
	exec proc_formate @month, @month output
	exec proc_formate @day, @day output
	exec proc_formate @hour, @hour output
	exec proc_formate @minute, @minute output
	exec proc_formate @second, @second output
	print @second
	set @id = @prefix + @year + @month + @day + @hour + @minute + @second
go

declare @haha varchar(17)
exec proc_createID 'emp', @haha output
print @haha

--3. 判斷用戶是否登陸成功: 1-用戶不存在 2-密碼錯誤 3-登錄成功
if exists (select 1 from sysobjects where name = 'proc_checkLogin')
	drop procedure proc_checkLogin
go
create procedure proc_checkLogin
	@name varchar(10),	--登錄名
	@pass varchar(8),	--密碼
	@type varchar(10),	--登錄類型
	@state int output	--登陸狀態
as
	--判斷是否用戶存在	
	if not exists (select 1 from TB_EMPLOYEE where empName = @name)
	begin
		set @state = 1
		return
	end
	--判斷用戶的密碼是否正確
	if not exists (select 1 from TB_EMPLOYEE where empName = @name and empPass = @pass)
	begin
		set @state = 2
		return
	end
	--判斷用戶是否有權限
	if not exists (select 1 from TB_EMPLOYEE as a inner join TB_EMPLOYEE_TYPE as b 
			on a.emtId = b.emtId where a.empName = @name and b.emtName = @type)	
	begin
		set @state = 3
		return
	end
	set @state = 4
go
	
--4. 顯示員工表中的所有項
if exists (select 1 from sysobjects where name = 'proc_listAllEmployee')
	drop procedure proc_listAllEmployee
go
create procedure proc_listAllEmployee
as
	select * from TB_EMPLOYEE
go

exec proc_listAllEmployee