工具类.sql

毕业设计作品--超市管理系统

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