用存储过程导出DBF文件

突然项目中需要用到这个功能，整了好长时间终于出来了，希望能够对像我一样的小白有所帮助，里面有的代码是可以变动的，可以变通

--调用

--EXEC dbo.MVC_GetDBF_ok @path = N'E:\AA',@fname = N'',@over = '1',@tname = 'XM',@tjbm = '1'

ALTER PROC MVC_GetDBF_ok

@path nvarchar(1000), --文件存放目录
@fname nvarchar(250),      --文件名,默认为表名
@over VARCHAR(50), --是否覆盖已经存在的文件,如果不覆盖,则直接追加
@tname VARCHAR(50),     --表名
@tjbm VARCHAR(1)  --统计部门
AS
BEGIN
DECLARE @Sqlstring NVARCHAR(MAX)
IF(@tname='XM')
BEGIN
SET @Sqlstring='SELECT xm_pk 项目代码,xm_name 项目名称  FROM dbo.XM_Master'
END
IF(@tname='Xm_mf')
BEGIN
SET @Sqlstring='SELECT id 分类号,[desc] 分类名称,sno 同类序号,nindex 编码索引,unit 计量单位,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmProperty'' and LDGUID=property) 物资属性,hid 高等分类号,hdesc 高等分类名,attr 属性 FROM dbo.Xm_mf_Master'
END
IF(@tname='Xm_mfDept')
BEGIN
SET @Sqlstring='SELECT id 部门代码,dept 使用部门,(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=aspect) 使用方向,location 安置地点,property 属性,(select username from smuser where usercode=custos) 保管人 from dbo.Xm_mfDept_Master'
END
IF(@tname='Xm_sb_Zjk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='select top 50000 id 主机编号,ItemName 品名,classId 分类号,tlxh 同类序号,itemId 仪器代码,model 型号,spec 规格,price 单价,quantity 数量,totalPrice 总价,purDt 购置日期,deptId 使用部门,bmmc 部门名称,Gbid 国别码,gm 国名,pjId 项目代码,
xmmc 项目名称,manufacturer 生产厂家,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmSource'' and LDGUID=sourceId) 设备来源,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmAt'' and LDGUID=expAccount) 经费科目,UsPrice 美元单价,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' and LDGUID=equipmentState) 设备现状,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' and LDGUID=useDirection) 使用方向,accountDt 入账日期,voucher 入账凭证,
(select username from smuser where usercode=userid) 设备管理员,formerlyId 原编号,annexQuantity 附件数量,accessoriesTprice 附件总价,
ManufactureDate 出厂日期,ManufactureNo 出厂号,supplier 供货商,unit 计量单位,putLocation 安置地点,(select username from smuser where usercode=keeper) 保管人,
rationDt 额定机时,useDt 使用机时,upkeep 维修费,archivesNo 档案号码,(select desc0 from SMLSTD where FK_LCODING=''ManageProperty'' and LDGUID=manageProperty) 管理属性,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmProperty'' and LDGUID=ItemProperty) 物资属性,remark 备注,crFlag 卡片标志,lable 标签标志 
from dbo.Xm_sb_zjk_Master where IsLowValue<>''Y'' order by id DESC'
END
ELSE
BEGIN
SET @Sqlstring='select top 50000  id 主机编号,ItemName 品名,classId 分类号,tlxh 同类序号,itemId 仪器代码,model 型号,spec 规格,price 单价,
quantity 数量,totalPrice 总价,purDt 购置日期,deptId 使用部门,bmmc 部门名称,Gbid 国别码,gm 国名,pjId 项目代码,
xmmc 项目名称,manufacturer 生产厂家,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmSource'' and LDGUID=sourceId) 设备来源,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmAt'' and LDGUID=expAccount) 经费科目,
UsPrice 美元单价,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' and LDGUID=equipmentState) 设备现状,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' and LDGUID=useDirection) 使用方向,accountDt 入账日期,voucher 入账凭证,
(select username from smuser where usercode=userid) 设备管理员,formerlyId 原编号,annexQuantity 附件数量,accessoriesTprice 附件总价,
ManufactureDate 出厂日期,ManufactureNo 出厂号,supplier 供货商,unit 计量单位,putLocation 安置地点,(select username from smuser where usercode=keeper) 保管人,
rationDt 额定机时,useDt 使用机时,upkeep 维修费,archivesNo 档案号码,(select desc0 from SMLSTD where FK_LCODING=''ManageProperty'' and LDGUID=manageProperty) 管理属性,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmProperty'' and LDGUID=ItemProperty) 物资属性,remark 备注,crFlag 卡片标志,lable 标签标志 from Xm_sb_zjk_Master  
where tjbm='''+@tjbm+''' order by id desc'
END
END
IF(@tname='XmAlterationItem')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='select top 50000  zwxh 帐务序号,bdxz 变动性质,accountDt 入账日期,id 主机编号,ItemName 品名,model 型号,spec 规格,classId 分类号,tlxh 同类序号,itemId 仪器代码,Gbid 国别码,gm 国名,purDt 购置日期,price 单价,quantity 数量,totalPrice 总价,
yPrice 原单价,yQuantity 原数量,yTotalPrice 原总价,deptId 使用部门,bmmc 部门名称,ydeptId 出入部门码,
(select dept from Xm_mfDept_Master where id=ydeptId)  出入部门码名称,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=useDirection) 使用方向,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=yUseDirection) 出入方向,
pjId 项目代码,(select xm_name from XM_Master where xm_pk=pjId) 项目名称,drdw 调入单位,voucher 入账凭证,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmSource'' AND LDGUID=sourceId) 设备来源,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=expAccount) 经费科目,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=equipmentState) 设备现状,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=yEquipmentState) 出入现状,(select username from smuser where usercode=userid) 设备管理员,
(select username from smuser where usercode=keeper) 保管人,formerlyId 原编号,manufacturer 生产厂家,ManufactureDate 出厂日期,ManufactureNo 出厂号,
supplier 供货商,UsPrice 美元单价,putLocation 安置地点,archivesNo 档案号码,annexQuantity 附件数量,accessoriesTprice 附件总价,yAnnexQuantity 原附件数量,yAccessoriesTprice 原附件总价,rationDt 额定机时,
useDt 使用机时,upkeep 维修费,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmProperty'' AND LDGUID=ItemProperty) 物资属性,unit 计量单位,remark 备注,bdrq 变动日期,bdxh 变动序号,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''ManageProperty'' AND LDGUID=manageProperty) 管理属性,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''ManageProperty'' AND LDGUID=yManageProperty) 原管理属性,yItemId 出入仪器号 
from XmAlterationItem_Master where IsLowValue<>''Y'''
END
ELSE
BEGIN
SET @Sqlstring='select top 50000  zwxh 帐务序号,bdxz 变动性质,accountDt 入账日期,id 主机编号,ItemName 品名,model 型号,spec 规格,classId 分类号,tlxh 同类序号,itemId 仪器代码,Gbid 国别码,gm 国名,purDt 购置日期,price 单价,quantity 数量,totalPrice 总价,
yPrice 原单价,yQuantity 原数量,yTotalPrice 原总价,deptId 使用部门,bmmc 部门名称,ydeptId 出入部门码,
(select dept from Xm_mfDept_Master where id=ydeptId)  出入部门码名称,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=useDirection) 使用方向,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=yUseDirection) 出入方向,
pjId 项目代码,(select xm_name from XM_Master where xm_pk=pjId) 项目名称,drdw 调入单位,voucher 入账凭证,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmSource'' AND LDGUID=sourceId) 设备来源,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=expAccount) 经费科目,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=equipmentState) 设备现状,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=yEquipmentState) 出入现状,(select username from smuser where usercode=userid) 设备管理员,
(select username from smuser where usercode=keeper) 保管人,formerlyId 原编号,manufacturer 生产厂家,ManufactureDate 出厂日期,ManufactureNo 出厂号,
supplier 供货商,UsPrice 美元单价,putLocation 安置地点,archivesNo 档案号码,annexQuantity 附件数量,accessoriesTprice 附件总价,yAnnexQuantity 原附件数量,yAccessoriesTprice 原附件总价,rationDt 额定机时,
useDt 使用机时,upkeep 维修费,(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''XmProperty'' AND LDGUID=ItemProperty) 物资属性,unit 计量单位,remark 备注,bdrq 变动日期,bdxh 变动序号,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''ManageProperty'' AND LDGUID=manageProperty) 管理属性,
(SELECT DESC0 FROM SMLSTD WHERE FK_LCODING=''ManageProperty'' AND LDGUID=yManageProperty) 原管理属性,yItemId 出入仪器号 
from XmAlterationItem_Master where tjbm='''+@tjbm+''''
END
END
IF(@tname='Xm_fjkp')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='select top 50000  fjxz 附件性质,fjid 附件编号,fjmc 附件名称,model 型号,spec 规格,fjdj 附件单价,yqbh 仪器编号,
zjmc 主机名称,sybm 使用部门,bmmc 部门名称,Gbid 国别码,gm 国名,purDt 购置日期,fjcj 附件厂家,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=fjzt) 附件现状,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=expAccount) 经费科目,rzrq 入账日期,rzpz 入账凭证,crFlag 卡片标志,
lable 附件标志,fjzy 附件摘要 from Xm_fjkp_Master'
END
ELSE
BEGIN
SET @Sqlstring='select top 50000  fjxz 附件性质,fjid 附件编号,fjmc 附件名称,model 型号,spec 规格,fjdj 附件单价,yqbh 仪器编号,
zjmc 主机名称,sybm 使用部门,bmmc 部门名称,Gbid 国别码,gm 国名,purDt 购置日期,fjcj 附件厂家,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=fjzt) 附件现状,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=expAccount) 经费科目,rzrq 入账日期,rzpz 入账凭证,crFlag 卡片标志,
lable 附件标志,fjzy 附件摘要 from Xm_fjkp_Master where tjbm='''+@tjbm+''''
END
END
IF(@tname='Xm_fjkbd')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='SELECT top 50000  zwxh 帐务序号,bdxz 变动性质,rzrq 入账日期,fjxh 附件序号,fjmc 附件名称,model 附件型号,spec 附件规格,
fjxz 附件性质,yfjxz 原附件性质,fjdj 附件单价,yfjdj 原附件单价,yqbh 仪器编号,zjmc 主机名称,yyqbh 原仪器编号,
yzjmc 原主机名称,bmdm 部门代码,sybm 使用部门,fjgb 附件国别,fjgm 附件国名,gzrq 购置日期,fjcj 附件厂家,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=fjzt) 附件现状,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=jfkm) 经费科目,rzpz 入账凭证,fjzy 附件摘要,yfjzy 原附件摘要,bdxh 变动序号,bdrq 变动日期,
zbxh 主变序号 FROM Xm_fjkbd_Master'
END
ELSE
BEGIN
SET @Sqlstring='SELECT top 50000  zwxh 帐务序号,bdxz 变动性质,rzrq 入账日期,fjxh 附件序号,fjmc 附件名称,model 附件型号,spec 附件规格,
fjxz 附件性质,yfjxz 原附件性质,fjdj 附件单价,yfjdj 原附件单价,yqbh 仪器编号,zjmc 主机名称,yyqbh 原仪器编号,
yzjmc 原主机名称,bmdm 部门代码,sybm 使用部门,fjgb 附件国别,fjgm 附件国名,gzrq 购置日期,fjcj 附件厂家,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=fjzt) 附件现状,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=jfkm) 经费科目,rzpz 入账凭证,fjzy 附件摘要,yfjzy 原附件摘要,bdxh 变动序号,bdrq 变动日期,
zbxh 主变序号 from Xm_fjkbd_Master where tjbm='''+@tjbm+''''
END
END
IF(@tname='XM_clk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='SELECT top 50000  id 车辆编号,ItemName 车辆名称,model 型号,spec 规格,classId 分类号,(select tlxh from Xm_sb_zjk_Master where Xm_sb_zjk_Master.id=XM_clk_Master.id) 同类序号,
price 单价,quantity 数量,totalPrice 总价,itemId 仪器代码,formerlyId 原编号,purDt 购置日期,accountDt 入账日期,
(select desc0 from SMLSTD where FK_LCODING=''XmState'' and LDGUID=equipmentState) 车辆现状,deptId 部门,
(select dept from Xm_mfDept_Master where Xm_mfDept_Master.id=XM_clk_Master.deptId) 使用部门,
(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=clqk) 车辆情况,pql 排气量升,xslc 行驶里程,pzhm 牌照号码,
(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=clyt) 车辆用途,remark 备注 
from XM_clk_Master'
END
ELSE
BEGIN
SET @Sqlstring='SELECT top 50000  id 车辆编号,ItemName 车辆名称,model 型号,spec 规格,classId 分类号,(select tlxh from Xm_sb_zjk_Master where Xm_sb_zjk_Master.id=XM_clk_Master.id) 同类序号,
price 单价,quantity 数量,totalPrice 总价,itemId 仪器代码,formerlyId 原编号,purDt 购置日期,accountDt 入账日期,
(select desc0 from SMLSTD where FK_LCODING=''XmState'' and LDGUID=equipmentState) 车辆现状,deptId 部门,
(select dept from Xm_mfDept_Master where Xm_mfDept_Master.id=XM_clk_Master.deptId) 使用部门,
(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=clqk) 车辆情况,pql 排气量升,xslc 行驶里程,pzhm 牌照号码,
(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=clyt) 车辆用途,remark 备注 
from XM_clk_Master WHERE id IN (SELECT id FROM dbo.Xm_sb_zjk_Master where tjbm='''+@tjbm+''')'
END
END
IF(@tname='XM_clbdk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='SELECT top 50000 zwxh 帐务序号,rzrq 入账日期,yqbh 仪器编号,yqmc 仪器名称,flh 分类号,tlxh 同类序号,dj 单价,sl 数量,zj 总价,(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=clqk) 车辆情况,
(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=yclqk) 原车辆情况,pql 排气量,xslc 行驶里程,yxslc 原行驶里程,pzhm 牌照号码,ypzhm 原牌照号码,
(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=yt) 用途,(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=yyt) 原用途,
bdxh 变动序号,zxrq 注销日期 from XM_clbdk_Master'
END
ELSE
BEGIN
SET @Sqlstring='select top 50000 zwxh 帐务序号,rzrq 入账日期,yqbh 仪器编号,yqmc 仪器名称,flh 分类号,tlxh 同类序号,dj 单价,sl 数量,zj 总价,(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=clqk) 车辆情况,
(select desc0 from SMLSTD where FK_LCODING=''VehicleCondition'' and LDGUID=yclqk) 原车辆情况,pql 排气量,xslc 行驶里程,yxslc 原行驶里程,pzhm 牌照号码,ypzhm 原牌照号码,
(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=yt) 用途,(select desc0 from SMLSTD where FK_LCODING=''VehicleUse'' and LDGUID=yyt) 原用途,
bdxh 变动序号,zxrq 注销日期 from XM_clbdk_Master WHERE yqbh IN (SELECT id FROM dbo.Xm_sb_zjk_Master where tjbm='''+@tjbm+''')'
END

END
IF(@tname='XM_fwk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='select top 50000 id 房屋编号,ItemName 房屋名称,model 型号,spec 规格,classId 分类号,(select tlxh from Xm_sb_zjk_Master where Xm_sb_zjk_Master.id=XM_fwk_Master.id) 同类序号,
itemId 仪器代码,price 单价,quantity 数量,totalPrice 总价,formerlyId 原编号,purDt 竣工日期,accountDt 入账日期,
(select desc0 from SMLSTD where FK_LCODING=''XmState'' and LDGUID=equipmentState) 房屋现状,deptId 部门,
(select dept from Xm_mfDept_Master where Xm_mfDept_Master.id=XM_fwk_Master.deptId) 使用部门,
(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=fwqk) 房屋情况,(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=gnfs) 供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=ssxz) 设施性质,symj 使用面积平方米,czmj 出租面积平方米,czhm 产证号码,remark 备注
from XM_fwk_Master'
END
ELSE
BEGIN
SET @Sqlstring='select top 50000 id 房屋编号,ItemName 房屋名称,model 型号,spec 规格,classId 分类号,(select tlxh from Xm_sb_zjk_Master where Xm_sb_zjk_Master.id=XM_fwk_Master.id) 同类序号,
itemId 仪器代码,price 单价,quantity 数量,totalPrice 总价,formerlyId 原编号,purDt 竣工日期,accountDt 入账日期,
(select desc0 from SMLSTD where FK_LCODING=''XmState'' and LDGUID=equipmentState) 房屋现状,deptId 部门,
(select dept from Xm_mfDept_Master where Xm_mfDept_Master.id=XM_fwk_Master.deptId) 使用部门,
(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=fwqk) 房屋情况,(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=gnfs) 供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=ssxz) 设施性质,symj 使用面积平方米,czmj 出租面积平方米,czhm 产证号码,remark 备注
from XM_fwk_Master WHERE id IN (SELECT id FROM dbo.Xm_sb_zjk_Master where tjbm='''+@tjbm+''')'
END

END
IF(@tname='XM_fwbdk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='select top 50000 zwxh 帐务序号,rzrq 入账日期,yqbh 仪器编号,yqmc 仪器名称,flh 分类号,tlxh 同类序号,dj 单价,sl 数量,zj 总价,(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=clqk) 房屋情况,
(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=yclqk) 原房屋情况,
(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=gnfs) 供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=ygnfs) 原供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=ssxz) 设施性质,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=yssxz) 原设施性质,symj 使用面积,ysymj 原使用面积,czmj 出租面积,yczmj 原出租面积,czhm 产证号码
from XM_fwbdk_Master'
END
ELSE
BEGIN
SET @Sqlstring='select top 50000 zwxh 帐务序号,rzrq 入账日期,yqbh 仪器编号,yqmc 仪器名称,flh 分类号,tlxh 同类序号,dj 单价,sl 数量,zj 总价,(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=clqk) 房屋情况,
(select desc0 from SMLSTD where FK_LCODING=''HouseCondition'' and LDGUID=yclqk) 原房屋情况,
(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=gnfs) 供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''HouseHeat'' and LDGUID=ygnfs) 原供暖方式,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=ssxz) 设施性质,
(select desc0 from SMLSTD where FK_LCODING=''FacilyNature'' and LDGUID=yssxz) 原设施性质,symj 使用面积,ysymj 原使用面积,czmj 出租面积,yczmj 原出租面积,czhm 产证号码
from XM_fwbdk_Master WHERE yqbh IN (SELECT id FROM dbo.Xm_sb_zjk_Master where tjbm='''+@tjbm+''')'
END
END
IF(@tname='Xm_jgk')
BEGIN
IF(@tjbm IS NULL OR @tjbm='')
BEGIN
SET @Sqlstring='SELECT top 50000  yqbh 仪器编号,flh 分类号,(select tlxh from Xm_sb_zjk_Master where id=yqbh) 同类序号,
yqmc 仪器名称,model 型号,spec 规格,yqdm 仪器代码,ccrq 出厂日期,
Gbid 国别码,gm 国名,dj 单价,mydj 美元单价,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=jfkm) 经费科目,gzrq 购置日期,rzrq 入账日期,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=sbxz) 设备现状,
bmdm 部门代码,bmmc 使用部门,(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=syfx) 使用方向,xmdm 项目代码,xmmc 项目名称,sccj 生产厂家,ghs 供货商,zxrq 注销日期,
jxjs 教学机时,kyjs 科研机时,qtjs 其他机时,bkspx 本科生培训,yjspx 研究生培训,jsbp 教师培训,
qtpx 其他培训,xksy 新开实验,gjsy 改进实验,kyxm 科研项目,wcxm 完成项目,gjjpj 国家级评奖,qthj 其他获奖,xncs 校内测试,
xwcs 校外测试,yygn 原有功能,yiygn 已用功能,xkgn 新开功能,whrs 维护人数,yqfzr 仪器负责人,edjs 额定机时,syjs 使用机时,
fjsl 附件数量,fjzj 附件总价,wxfy 维修费用,wxcs 维修次数,
(select username from smuser where usercode=bgr) 保管人,azdd 安置地点,hthm 合同号码,dahm 档案号码,yyycg 年应用成果,
remark 备注,kpbz 卡片标志,pjdf 评价得分,tjnd 统计年度 
from Xm_jgk_Master where tjnd=year(getdate())'
END
ELSE
BEGIN
SET @Sqlstring='SELECT top 50000  yqbh 仪器编号,flh 分类号,(select tlxh from Xm_sb_zjk_Master where id=yqbh) 同类序号,
yqmc 仪器名称,model 型号,spec 规格,yqdm 仪器代码,ccrq 出厂日期,
Gbid 国别码,gm 国名,dj 单价,mydj 美元单价,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmAt'' AND LDGUID=jfkm) 经费科目,gzrq 购置日期,rzrq 入账日期,
(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmState'' AND LDGUID=sbxz) 设备现状,
bmdm 部门代码,bmmc 使用部门,(SELECT DESC0 FROM dbo.SMLSTD WHERE FK_LCODING=''XmUs'' AND LDGUID=syfx) 使用方向,xmdm 项目代码,xmmc 项目名称,sccj 生产厂家,ghs 供货商,zxrq 注销日期,
jxjs 教学机时,kyjs 科研机时,qtjs 其他机时,bkspx 本科生培训,yjspx 研究生培训,jsbp 教师培训,
qtpx 其他培训,xksy 新开实验,gjsy 改进实验,kyxm 科研项目,wcxm 完成项目,gjjpj 国家级评奖,qthj 其他获奖,xncs 校内测试,
xwcs 校外测试,yygn 原有功能,yiygn 已用功能,xkgn 新开功能,whrs 维护人数,yqfzr 仪器负责人,edjs 额定机时,syjs 使用机时,
fjsl 附件数量,fjzj 附件总价,wxfy 维修费用,wxcs 维修次数,
(select username from smuser where usercode=bgr) 保管人,azdd 安置地点,hthm 合同号码,dahm 档案号码,yyycg 年应用成果,
remark 备注,kpbz 卡片标志,pjdf 评价得分,tjnd 统计年度 
from Xm_jgk_Master where yqbh IN (SELECT id FROM dbo.Xm_sb_zjk_Master where tjbm='''+@tjbm+''') and 
tjnd=year(getdate())'
END
END

declare @sqlstr varchar(max)
SET @sqlstr=@Sqlstring

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)


--参数检测
if isnull(@fname,'')='' set @fname=@tname+'.dbf'


--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
begin
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
end
else
set @over=0
else
set @over=1


--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'


--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)


--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr


exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr


--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint','DECIMAL(18,2)','DECIMAL') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)


if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
end


exec @err=sp_oadestroy @obj


set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'


--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')


set @sql='drop table ['+@tbname+']'
exec(@sql)


return


lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
DROP TABLE #tb


END