CREATE TABLE [dbo].[yq_Project](

    [ID] [int] IDENTITY(1,1) primary key,

    [cCode] [varchar](32) NOT NULL,

    [cPriorCode] [varchar](32) NULL,

    [cName] [nvarchar](32) NULL,

    [cPersonName] [nvarchar](15) NULL,

    [cAddr] [nvarchar](20) NULL,

    [cTel] [varchar](20) NULL,

    [bHead] [bit] NULL,

    seq int )

alter table yq_project add cMemo nvarchar(128)

create table yq_Qz_House

(

id int identity(1,1) primary key,

cHuHao varchar(16),

cDongHao varchar(16),

cZuoLuo nvarchar(64),

cJieGou nvarchar(32),

cCengCi varchar(16),

cWeiZhi nvarchar(32),

cChaoXiang nvarchar(16),

iYuanLuo decimal(18,2),

dJunGongShiJian datetime,

cLeiBie nvarchar(16),

iZhuFang decimal(18,2),

iFengBiYangTai decimal(18,2),

iWeiFengYangTai decimal(18,2),

iGongYongFenTan decimal(18,2),

cProCode varchar(32)

)

if exists(select name from sysobjects where name='Prants')

 drop function Prants

go

create function Prants(@cCode varchar(32))

returns varchar(100)

as

begin

 declare @str varchar(100)

 set @str=''

 declare @num varchar(32)

 select @str= cName from yq_project  where cCode=@cCode

 if exists(select cPriorCode from yq_project where cCode=@cCode)

 begin

  select @num=cPriorCode from yq_project where cCode=@cCode

  set @str=@str+'/'+dbo.Prants(@num)

 end

 return @str

end