--转为日期
alter FUNCTION f_num2date ( @num varchar(8) )
RETURNS varchar(10)
AS BEGIN
DECLARE @re varchar(10)
SET @re = '1900-01-01'
IF ( LEN(@num) = 8 )
BEGIN
SELECT @re = SUBSTRING(@num, 1, 4) + '-' + SUBSTRING(@num, 5, 2)
+ '-' + SUBSTRING(@num, 7, 2)
END
ELSE
IF ( LEN(@num) = 6 )
BEGIN
SELECT @re = SUBSTRING(@num, 1, 4) + '-' + SUBSTRING(@num, 5, 2)+'-01'
END
ELSE
BEGIN
RETURN @re
END
RETURN @re
end
--籍贯名称转为代码等
alter function f_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from BM_AB
where [MC0000] = @name )
begin
select @re = [BM0000]
from BM_AB
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--民族名称转为代码
create function f_bm_ae_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_AE]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_AE]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--政治面貌名称转为代码
create function f_BM_AT_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from BM_AT
where [MC0000] = @name )
begin
select @re = [BM0000]
from BM_AT
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
----
--婚姻名称转为代码
create function f_BM_BG_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_BG]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_BG]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--身体状况名称转为代码
create function f_BM_BF_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_BF]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_BF]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--个人身份名称转为代码
create function f_BM_BK_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_BK]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_BK]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--文化程度名称转为代码
create function f_BM_AM_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_AM]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_AM]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--专业名称转为代码
create function f_BM_AI_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_AI]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_AI]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--专业技术职务名称转为代码
create function f_BM_AJ_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_AJ]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_AJ]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
--取得资格途径名称转为代码
create function f_BM_BY_name2id(@name nvarchar(50))
returns char(10)
as
begin
DECLARE @re varchar(10)
if exists ( select *
from [BM_BY]
where [MC0000] = @name )
begin
select @re = [BM0000]
from [BM_BY]
where [MC0000] = @name
end
else
begin
select @re = ''
end
RETURN @re
end
/**/
--通用名称转为ID代码
alter proc pt_MC00002BM0000
(
@table varchar(20),
@MCname varchar(50),
@out1 char(10) output
)
as
begin
DECLARE @sql varchar(1000)
set @sql = '
if exists ( select *
from ['+@table+']
where [MC0000] = '''+@MCname+''' )
begin
select [BM0000]
from ['+@table+']
where [MC0000] = '''+@MCname+'''
end
'
exec (@sql)
end
--DECLARE @getval char(10)
--exec pt_MC00002BM0000 'BM_BY','考试', @getval output
--select @getval
