小白问题！！！

Omg_Skyer 2016-06-07 06:11:22

已知字段的数据类型，能不能查询对应的字段名呢？？

...全文

208 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

Omg_Skyer 2016-06-08

打赏
举报

找到问题了，数值类型的字段，插入了

''

导致报错

中国风 2016-06-08

打赏
举报

引用 3 楼 Omg890405 的回复:

还有一个问题，从一个表a查询数据插入另一表b，从数据类型 varchar 转换为 numeric 时出错。，但是a，b都不含 numeric 类型数据。。。。。为什么会这样？？？

比较两个表的本栏位及类型是否一致不一致时，检查值是否满足 ISNUMERIC--用这个检查字符串是否有不满足的列 --比如列2为字符，导入表对应列为数字时，检查值 SELECT * FROM TABLENAME WHERE ISNUMERIC(列2)=0

Omg_Skyer 2016-06-08

打赏
举报

 create proc 发票001
@FBillNo nvarchar (255)
as
  IF  EXISTS  (select *from dbo.ICStockBill b where b.FRelateInvoiceID=0 and @FBillNo = b.FBillNo)
begin
  declare @tmp int 
select  @tmp = max (cast(FRelateInvoiceID as int)) from dbo.ICStockBill
update ICStockBill set FRelateInvoiceID = (@tmp+1)
where @FBillNo = ICStockBill .FBillNo
 declare @sql nvarchar(510) 
select @sql=FDesc from ICBillNo where fbillid = 86
DECLARE @TmpID INT 
DECLARE @fprojectval varchar(80) 
select  @fprojectval='' 
SET @TmpID = (SELECT FID FROM t_BillCodeRule 
WITH(READUNCOMMITTED) WHERE fbilltypeid='86' and fprojectid=3)
update t_billcoderule set fprojectval = fprojectval+1,@fprojectval=isnull(fprojectval,1),
flength=case when (flength-len(fprojectval)) >= 0 then flength else len(fprojectval) end where FID = @TmpID 
Update ICBillNo Set FCurNo = @fprojectval where fbillid = 86


INSERT INTO ICSale(FInterID,  FBillNo,FBrNo, FTranType,FCancellation,FStatus,FDiscountType,FROB,  FClassTypeID,FSubSystemID,FYear,FPeriod,FItemClassID,FFincDate,FHookStatus,Fdate,FSettleID,FCurrencyID,FCheckDate,FConfirmDate,FDeptID, FEmpID, FBillerID,   FConfirmor,FExchangeRateType,FExchangeRate,FCompactNo,FSaleStyle,FAcctID,FMultiCheckDate1,FMultiCheckDate2,FVchInterID,     FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FPOOrdBillNo,FMultiCheckDate6,FYearPeriod,FYtdIntRate,FNote,FOrgBillInterID,FPrintCount,FImport,FHookerID,  FSelTranType, FConfirmAdvice,FBrID, FManagerID,FCussentAcctID,FSettleDate, FJSBillNo,FSysStatus,FPayCondition) 
select      FRelateInvoiceID, @sql,   FBrNo, 86,           0,         0,              0,   FROB , 1000000,        0 ,       2008 , 2 ,        1    ,    Fdate ,       0,     Fdate ,1  ,     FCurrencyID ,null    ,  null ,      FDeptID, FEmpID, FBillerID  , 0,             1 ,               1,           null ,  FSaleStyle, FAcctID,     null          ,null,             0 ,             null,           null,                null,           null,         null,          '',         '' ,     '',           0,         0,        0,        0,            21,       '',          0,    FFManagerID,FCussentAcctID,fdate,         '',     0,         FPayCondition
from ICStockBill where @FBillNo = ICStockBill .FBillNo

INSERT INTO ICSaleEntry (FInterID,FEntryID,FBrNo,                  FMapNumber,FMapName,FItemID, FAuxPropID,FQty,  FUnitID,  Fauxqty, FSecCoefficient,FSecQty,  Fauxprice,FDiscountRate,FUniDiscount,FAmtDiscount,   FStdAmtDiscount, FAuxPriceDiscount, FKFDate,FKFperiod, FPeriodDate,FBatchNo,FNote,                 Famount,FStdAmount,FTaxRate, FTaxAmount, FStdTaxAmount,FOrgBillEntryID,FOrderPrice,FAuxOrderPrice,FClassID_SRC,  FEntryID_SRC,   FSourceBillNo, FSourceTranType,     FSourceInterId,             FSourceEntryID, FContractBillNo,FContractInterID,FContractEntryID,  FOrderBillNo, FOrderInterID, FOrderEntryID, FAllHookQTY,FStdAllHookAmount,FCurrentHookQTY,FStdCurrentHookAmount,FPlanMode, FMTONo,  FConfirmAdvice,  FSEOutBillNo,FSEOutEntryID,FSEOutInterID)  
select                  (@tmp+1) ,FEntryID,ICStockBillEntry .FBrNo, '' ,           '', FItemID, FAuxPropID,FQty, FUnitID,  Fauxqty, FSecCoefficient,FSecQty , Fauxprice,FDiscountRate ,null,       FDiscountAmount,FDiscountAmount ,    '',            FKFDate,FKFperiod, FPeriodDate,FBatchNo,ICStockBillEntry.FNote,Famount, Famount,   FTaxRate, FTaxAmount,  FTaxAmount ,       0,         Fauxprice, Fauxprice  ,      0,                  0,            '',               '',        ICStockBillEntry .FInterID,      FEntryID  ,        '',                0 ,              0 ,        FOrderBillNo,  FOrderInterID, FOrderEntryID, 0,              0,              0 ,                         0,   FPlanMode, FMTONo,          '' ,      FSEOutBillNo, FSEOutEntryID ,FSEOutInterID   
from ICStockBillEntry join ICStockBill on ICStockBillEntry.FInterID=ICStockBill.FInterID 
where @FBillNo = ICStockBill .FBillNo


update icsale set FBillNo=replace(FBillNo,'+','') where FInterID=(@tmp+1)
update ICSaleEntry set FSourceBillNo =@FBillNo where FInterID=(@tmp+1)
update  ICSaleEntry set FSourceTranType= (select FTranType from ICStockBill where @FBillNo = ICStockBill .FBillNo)
where FInterID=(@tmp+1)

end
ELSE  select '已有发票'


exec 发票001 'sOUT000004'
exec 发票001 'xOUT000001'
drop proc 发票001

两条插入语句都报错

Omg_Skyer 2016-06-08

打赏
举报

create proc 发票001 @FBillNo nvarchar (255) as IF EXISTS (select *from dbo.ICStockBill b where b.FRelateInvoiceID=0 and @FBillNo = b.FBillNo) begin declare @tmp int select @tmp = max (cast(FRelateInvoiceID as int)) from dbo.ICStockBill update ICStockBill set FRelateInvoiceID = (@tmp+1) where @FBillNo = ICStockBill .FBillNo declare @sql nvarchar(510) select @sql=FDesc from ICBillNo where fbillid = 86 DECLARE @TmpID INT DECLARE @fprojectval varchar(80) select @fprojectval='' SET @TmpID = (SELECT FID FROM t_BillCodeRule WITH(READUNCOMMITTED) WHERE fbilltypeid='86' and fprojectid=3) update t_billcoderule set fprojectval = fprojectval+1,@fprojectval=isnull(fprojectval,1), flength=case when (flength-len(fprojectval)) >= 0 then flength else len(fprojectval) end where FID = @TmpID Update ICBillNo Set FCurNo = @fprojectval where fbillid = 86 INSERT INTO ICSale(FInterID, FBillNo,FBrNo, FTranType,FCancellation,FStatus,FDiscountType,FROB, FClassTypeID,FSubSystemID,FYear,FPeriod,FItemClassID,FFincDate,FHookStatus,Fdate,FSettleID,FCurrencyID,FCheckDate,FConfirmDate,FDeptID, FEmpID, FBillerID, FConfirmor,FExchangeRateType,FExchangeRate,FCompactNo,FSaleStyle,FAcctID,FMultiCheckDate1,FMultiCheckDate2,FVchInterID, FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FPOOrdBillNo,FMultiCheckDate6,FYearPeriod,FYtdIntRate,FNote,FOrgBillInterID,FPrintCount,FImport,FHookerID, FSelTranType, FConfirmAdvice,FBrID, FManagerID,FCussentAcctID,FSettleDate, FJSBillNo,FSysStatus,FPayCondition) select FRelateInvoiceID, @sql, FBrNo, 86, 0, 0, 0, FROB , 1000000, 0 , 2008 , 2 , 1 , Fdate , 0, Fdate ,1 , FCurrencyID ,null , null , FDeptID, FEmpID, FBillerID , 0, 1 , 1, null , FSaleStyle, FAcctID, null ,null, 0 , null, null, null, null, null, '', '' , '', 0, 0, 0, 0, 21, '', 0, FFManagerID,FCussentAcctID,fdate, '', 0, FPayCondition from ICStockBill where @FBillNo = ICStockBill .FBillNo INSERT INTO ICSaleEntry (FInterID,FEntryID,FBrNo, FMapNumber,FMapName,FItemID, FAuxPropID,FQty, FUnitID, Fauxqty, FSecCoefficient,FSecQty, Fauxprice,FDiscountRate,FUniDiscount,FAmtDiscount, FStdAmtDiscount, FAuxPriceDiscount, FKFDate,FKFperiod, FPeriodDate,FBatchNo,FNote, Famount,FStdAmount,FTaxRate, FTaxAmount, FStdTaxAmount,FOrgBillEntryID,FOrderPrice,FAuxOrderPrice,FClassID_SRC, FEntryID_SRC, FSourceBillNo, FSourceTranType, FSourceInterId, FSourceEntryID, FContractBillNo,FContractInterID,FContractEntryID, FOrderBillNo, FOrderInterID, FOrderEntryID, FAllHookQTY,FStdAllHookAmount,FCurrentHookQTY,FStdCurrentHookAmount,FPlanMode, FMTONo, FConfirmAdvice, FSEOutBillNo,FSEOutEntryID,FSEOutInterID) select (@tmp+1) ,FEntryID,ICStockBillEntry .FBrNo, '' , '', FItemID, FAuxPropID,FQty, FUnitID, Fauxqty, FSecCoefficient,FSecQty , Fauxprice,FDiscountRate ,null, FDiscountAmount,FDiscountAmount , '', FKFDate,FKFperiod, FPeriodDate,FBatchNo,ICStockBillEntry.FNote,Famount, Famount, FTaxRate, FTaxAmount, FTaxAmount , 0, Fauxprice, Fauxprice , 0, 0, '', '', ICStockBillEntry .FInterID, FEntryID , '', 0 , 0 , FOrderBillNo, FOrderInterID, FOrderEntryID, 0, 0, 0 , 0, FPlanMode, FMTONo, '' , FSEOutBillNo, FSEOutEntryID ,FSEOutInterID from ICStockBillEntry join ICStockBill on ICStockBillEntry.FInterID=ICStockBill.FInterID where @FBillNo = ICStockBill .FBillNo update icsale set FBillNo=replace(FBillNo,'+','') where FInterID=(@tmp+1) update ICSaleEntry set FSourceBillNo =@FBillNo where FInterID=(@tmp+1) update ICSaleEntry set FSourceTranType= (select FTranType from ICStockBill where @FBillNo = ICStockBill .FBillNo) where FInterID=(@tmp+1) end ELSE select '已有发票' exec 发票001 'sOUT000004' exec 发票001 'xOUT000001' 两条插入语句都有问题（金蝶K3的测试数据库）