系统支持过程.sql

水费管理系统

/*
	功能：生成序号

	注意：此过程在@nextid达到int型的最大值时将出现溢出问题，应该在这个问题出现前将其阻止。定期进行检测。
*/

if exists(select * from sysobjects where type='P' and name = 'get_nextid')
	drop proc get_nextid
go
create proc get_nextid(@name varchar(50) , @name1 varchar(50)='')
as
declare @ret int

select @ret = id from object_id where tbl_name=@name and code= @name1

if @@error<>0
begin
	return -1
end
if @ret is null
begin
	set @ret = 0
	insert into object_id(tbl_name, id, code) values(@name, @ret, @name1)
end

set @ret = @ret + 1
if @@error <> 0
	return -1

update object_id set id = id+1 where tbl_name=@name and code = @name1
if @@error<>0
begin
	return -1
end

return @ret

GO

/*
	功能：验证代码的有效性
	操作：从code_name表中检索指定类别和指定代码的记录，如果有则返回1，否则返回0
*/

if exists(select * from sysobjects where type='FN' and name = 'uf_is_valid_code')
	drop function uf_is_valid_code
go

create function uf_is_valid_code(@code varchar(40), @code_type varchar(40))
returns tinyint
as
begin
	if @code is null
		return 0
	
	declare @row_cnt int
	declare @error_var int, @rowcount_var int
	
	-- 验证代码的存在性
	select @row_cnt = count(*)
		from code_name
		where code_type = @code_type
				and standard_code = @code
	
	select @error_var = @@error, @rowcount_var = @@rowcount
	if @error_var <> 0
	begin
		return 0
	end
	
	if @row_cnt <> 1
	begin
		return 0
	end
	return 1
end
go

/*
	检查用户的存在性
*/
if exists(select * from sysobjects where type='FN' and name = 'uf_is_valid_cust')
	drop function uf_is_valid_cust
go

create function uf_is_valid_cust(@cust_code varchar(20))
returns tinyint
as
begin
	if @cust_code is null
		return 0
	
	declare @row_cnt int
	declare @error_var int, @rowcount_var int
	
	-- 验证代码的存在性
	select @row_cnt = count(*)
		from cust
		where cust_code = @cust_code
	
	select @error_var = @@error, @rowcount_var = @@rowcount
	if @error_var <> 0
	begin
		return 0
	end
	
	if @row_cnt <> 1
	begin
		return 0
	end
	return 1
end
go


/*
	功能:确定用户存在性
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_exist_cust')
	drop proc wsp_exist_cust
go

create proc wsp_exist_cust(@cust_code varchar(20))
as
if @cust_code is null
	return 0

-- 在cust表中查找
if exists(select * from cust where cust_code = @cust_code)
	return 1
-- 在cust_log表中查找
if exists(select * from cust_log where cust_code = @cust_code)
	return 1

go


/*
	功能：取得系统启用时间
*/
if exists(select * from sysobjects where type= 'P' and name = 'wsp_get_sys_start_time')
	drop proc wsp_get_sys_start_time
go

create proc wsp_get_sys_start_time(@st varchar(20) output)
as
begin
	declare @dt datetime
	declare @str varchar(20)

	select @dt = convert(datetime, standard_code) 
			from code_name 
			where code_type='SYS_START_TIME'

	if @dt is null
		set @dt = '1900-01-01'
	
	set @str = convert(varchar(40),@dt, 120)
	set @str = substring(@str, 1, len(@str) - 9)
	set @st = @str
end
go

/*
	功能：取得系统当前时间
*/
if exists(select * from sysobjects where type= 'P' and name = 'wsp_get_sys_time')
	drop proc wsp_get_sys_time
go

create proc wsp_get_sys_time(@time varchar(20) output)
as
begin
	declare @dt datetime
	declare @str varchar(20)

	set @dt = getdate()
	
	set @str = convert(varchar(40),@dt, 120)
	set @str = substring(@str, 1, len(@str) - 9)
	set @time = @str
end
go


/*
	功能：返回指定日期所在月份的最大天数
*/

if exists(select * from sysobjects where type='FN' and name = 'uf_max_days')
	drop function uf_max_days
go

create function uf_max_days(@date datetime)
returns tinyint
as
begin
	if @date is null
	begin
		return -1
	end

	declare @tmp_date datetime, @year int, @month tinyint, @day tinyint
	
	set @year = year(@date)
	set @month = month(@date)
	if @month = 12
	begin
		set @year = @year + 1
		set @month = 1
	end
	else
	begin
		set @month = @month + 1
	end

	set @tmp_date = ltrim(str(@year, 10)) + '-' + ltrim(str(@month, 2)) + '-01'
	set @tmp_date = dateadd(day, -1, @tmp_date)
	
	set @day = day(@tmp_date)
	return @day
end
go

if exists(select * from sysobjects where name = 'wsp_auto_exec' and type='P')
	drop proc wsp_auto_exec
go

create proc wsp_auto_exec
as
begin
	print 'exec wsp_auto_exec'
end
go