-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 会员自定义资料信息表
-- ==============================

--创建会员自定义资料信息表
if not exists(select name from sysobjects where name='PPFOR_TZB_CustomMember' and type='U')
	create table PPFOR_TZB_CustomMember
	(
		[_ID] int identity(1000,1) not null primary key,				--自动编号			
		[_MemberID] int not null unique REFERENCES [PPFOR_TZB_Member]([_ID]) ON DELETE CASCADE  --会员ID 	
	)
PPFOR_GO;

--获取信息
if exists(select name from sysobjects where name='PPFOR_TZB_CustomMember_GetModel' and type='P')
	drop procedure PPFOR_TZB_CustomMember_GetModel
PPFOR_GO;
create procedure PPFOR_TZB_CustomMember_GetModel(@_MemberID int)
as
	select * from PPFOR_TZB_CustomMember where [_MemberID]=@_MemberID
PPFOR_GO;

--创建添加和更新数据的存储过程
if exists(select name from sysobjects where name='PPFOR_TZB_CustomMember_SetProcedure' and type='P')
	drop procedure PPFOR_TZB_CustomMember_SetProcedure
PPFOR_GO;
create procedure PPFOR_TZB_CustomMember_SetProcedure
as
declare @colName varchar(32)
declare @colType varchar(32)
declare @length int
declare @s char(1)
declare @c char(1)
declare @index int
declare @names varchar(8000)
declare @paraNames varchar(8000)
declare @paraNames2 varchar(8000)
declare @params varchar(8000)
declare @sql varchar(8000) 
	begin
		declare pcurr cursor for
			SELECT [name],type_name([xtype]),[length] FROM syscolumns WHERE ([id] = (SELECT MAX(id)
     		FROM sysobjects WHERE [xtype] = 'u' AND [name] = 'PPFOR_TZB_CustomMember'))
	
			set @s=','
			set @c='@'
			set @index=0
			open pcurr
			fetch next from pcurr into @colName,@colType,@length
			while (@@fetch_status = 0)
			begin			
			 	if @colName is not null
				begin
					set @index=@index +1
					if(@index>2 and @index<=102)
					begin
						if @colType='bit' or @colType='int' or @colType='datetime' or @colType='ntext' 
							or @colType='varchar' or @colType='nvarchar' or @colType='numeric'
						begin	
							if @paraNames is not null
								set @paraNames=@paraNames + @s + @c + @colName
							else
								set @paraNames= @c + @colName
							if @paraNames2 is not null
								set @paraNames2=@paraNames2 + @s + '[' + @colName + ']=' + @c + @colName
							else
								set @paraNames2= 'set [' + @colName + ']=' + @c + @colName
							if @names is null
								set @names='[' + @colName + ']'
							else
								set @names=@names + @s + '[' + @colName + ']'
							if @params is null
								set @params= @c + @colName
							else
								set @params=@params + @s + @c + @colName
							if @colType='bit' or @colType='int' or @colType='datetime' or @colType='ntext'
								set @paraNames=@paraNames + ' ' + @colType
							else if @colType='nvarchar'
								set @paraNames=@paraNames + ' nvarchar(' + str(@length/2) + ')'
							else if @colType='varchar'
								set @paraNames=@paraNames + ' varchar(' + str(@length) + ')'
							else if @colType='numeric'
								set @paraNames=@paraNames + ' numeric(14,4)'
						end		
					end		
				end		
			 	fetch next from pcurr into @colName,@colType,@length
			end
			close pcurr
			deallocate pcurr 
			if object_id('PPFOR_TZB_CustomMember_Update', 'P') is not null
				set @sql='alter procedure [PPFOR_TZB_CustomMember_Update]' 
			else
				set @sql='create procedure [PPFOR_TZB_CustomMember_Update]' 
			set @sql=@sql + '(@_MemberID int,'
			set @sql=@sql + @paraNames + ') as'
			set @sql=@sql + ' if not exists(select [_ID] from [PPFOR_TZB_CustomMember] where [_MemberID]=@_MemberID)'
			
			set @sql=@sql + ' begin   insert into [PPFOR_TZB_CustomMember](_MemberID,' + @names + ') values (@_MemberID,'
			set @sql=@sql + @params + ') end'
			
			set @sql=@sql + ' else begin update [PPFOR_TZB_CustomMember] ' + @paraNames2
			set @sql=@sql + ' where [_MemberID]=@_MemberID end '
			exec(@sql)
	end
PPFOR_GO;
