存储过程中如果需要将一个运算结果作为别名应该怎么做？

[panzervor]

从ERP中调取数据，有关发票信息的，但是问题是我在平台上要将若干年的同一个表和在一起，但是ERP里面是按年份为帐套的，所以我导入一个表的时候必须在SBVID字段前面再加一个变量，但是为什么我这样操做 SELECT 2009+A.SBVID AS A.SBVID1 提示SBVID1无效呢？请各位前辈帮忙解决，谢谢。

CREATE  PROCEDURE Exp_SpSaleBillAuto
AS
DECLARE @Value VARCHAR(50)
DECLARE @Sql VARCHAR(8000)
DECLARE @DBSign INT
DECLARE @a INT

SELECT @Value = [Value], @DBSign = DBSign FROM Pub_Option WHERE [Key]='UFData'

SET @a=0

SET @Sql = '
	INSERT INTO Exp_SaleBill(
		 SaleBillID
		,SaleBillCode
		,OutBillCode
		,OrderBillCode
		,VouchTypeID
		,VouchTypeName
		,SaleTypeID
		,SaleTypeName
		,BillDate
		,DepName
		,PersonName
		,CusCode
		,CurrencyName
		,CurrencyRate
		,TaxRate
		,MakerName
		,BusiTypeName
		,DBSign
	) 

	SELECT
		2009+A.SBVID AS A.SBVID1--流水号
		,A.cSbVCode--发票号
		,RTRIM(A.cDLCode)
		,A.cSoCode
		,A.cVouchType --都是普通发票
		,''普通发票''
		,A.cSTCode--销售类型
		,''国外销售''
		,A.dDate--开票
		,B.Cdepname--销售部门
		,C.CpersonName--业务员
		,A.cCusCode--客户
		,A.cexCh_name--币种
		,A.iExchRate--汇率
		,A.iTaxRate--税率
		,A.cMaker--制单
		,A.cBusType--业务类型
		,' + CAST(@DBSign AS VARCHAR) + '
	FROM ' + @Value + 'SaleBillVouch A
	LEFT JOIN ' + @Value + 'Department B ON A.CDepCode = B.CDepCode
	LEFT JOIN ' + @Value + 'person C ON A.cPersonCode = C.cPersonCode
	WHERE cStCode = 1 AND A.SBVID1 NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @Sql

SET @Sql = '
	INSERT INTO Exp_SaleBillDetail(
		 DetailID
		,SaleBillID
		,StoreID
		,InvCode
		,Qty
		,UnitPrice
		,TaxUnitPrice
		,NoMoney
		,TaxMoney
		,SumMoney
		,DBSign
	) 
	SELECT 
		
		 AutoID --流水号
		,2009+SbVid AS SBVID1--外建
		,cWhCode--仓库号
		,cInvCode--存货编码
		,iQuantity--数量
		,iUnitPrice--无税单价
		,iTaxUnitPrice --含税单价
		,iMoney --无税金额
		,iTax --税额
		,iSum --价税合计
		,' + CAST(@DBSign AS VARCHAR) + '
	FROM ' + @Value + 'SaleBillVouchs A
	WHERE SBVid In(Select SBVid FROM '+ @Value + 'SaleBillVouch Where  cStCode = 1) AND A.AutoID NOT IN(SELECT DetailID From Exp_SaleBillDetail WHERE DBSign = ' + CAST(@DBSign AS VARCHAR) + ')'
EXEC(@Sql)
print @Sql

--更新字表中的ID
UPDATE A SET A.ID = B.ID
FROM Exp_SaleBillDetail A
INNER JOIN Exp_SaleBill B ON A.SaleBillID = B.SaleBillID AND B.DBSign = @DBSign
WHERE A.DBSign = @DBSign
GO

[华夏小卒]

SELECT
2009+A.SBVID AS A.SBVID1--流水号


不要加A。了吧

直接
SELECT
2009+A.SBVID AS SBVID1--流水号

[Sharon_liu]

同一个层查询中SELECT里定义的别名是不能在WHERE后使用的，必须使用原列名或放到不同的层中

[王向飞]

SELECT 2009+A.SBVID AS  SBVID1 

[Sharon_liu]

比如
SELECT A AS A1 FROM TB WHERE A1=1
是不对的，应该改为
SELECT A AS A1 FROM TB WHERE A=1
或者
SELECT A1 FROM (
SELECT A AS A1 FROM TB
) T WHERE A1=1

[wujinyuan]

不能加A了..加A等引用表列名.

[panzervor]

[code=sql]
服务器: 消息 207，级别 16，状态 3，行 2
列名 'SBVID1' 无效。

INSERT INTO Exp_SaleBill(
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
)

SELECT
2009+A.SBVID AS SBVID1 --流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,'普通发票'
,A.cSTCode--销售类型
,'国外销售'
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,999
FROM UFDATA_999_2010.dbo.SaleBillVouch A
LEFT JOIN UFDATA_999_2010.dbo.Department B ON A.CDepCode = B.CDepCode
LEFT JOIN UFDATA_999_2010.dbo.person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND A.SBVID1 NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = 999)

（所影响的行数为 0 行）


INSERT INTO Exp_SaleBillDetail(
DetailID
,SaleBillID
,StoreID
,InvCode
,Qty
,UnitPrice
,TaxUnitPrice
,NoMoney
,TaxMoney
,SumMoney
,DBSign
)
SELECT

AutoID --流水号
,2009+SbVid AS SBVID1--外建
,cWhCode--仓库号
,cInvCode--存货编码
,iQuantity--数量
,iUnitPrice--无税单价
,iTaxUnitPrice --含税单价
,iMoney --无税金额
,iTax --税额
,iSum --价税合计
,999
FROM UFDATA_999_2010.dbo.SaleBillVouchs A
WHERE SBVid In(Select SBVid FROM UFDATA_999_2010.dbo.SaleBillVouch Where cStCode = 1) AND A.AutoID NOT IN(SELECT DetailID From Exp_SaleBillDetail WHERE DBSign = 999)

（所影响的行数为 0 行）
[/sql]

[panzervor]

加A貌似有点多此一举，但是不加A也不能用啊，提示如上

[永生天地]

INSERT INTO Exp_SaleBill(
SaleBillID
,SaleBillCode
,OutBillCode
,OrderBillCode
,VouchTypeID
,VouchTypeName
,SaleTypeID
,SaleTypeName
,BillDate
,DepName
,PersonName
,CusCode
,CurrencyName
,CurrencyRate
,TaxRate
,MakerName
,BusiTypeName
,DBSign
)

SELECT
2009+A.SBVID AS SBVID1 --流水号
,A.cSbVCode--发票号
,RTRIM(A.cDLCode)
,A.cSoCode
,A.cVouchType --都是普通发票
,'普通发票'
,A.cSTCode--销售类型
,'国外销售'
,A.dDate--开票
,B.Cdepname--销售部门
,C.CpersonName--业务员
,A.cCusCode--客户
,A.cexCh_name--币种
,A.iExchRate--汇率
,A.iTaxRate--税率
,A.cMaker--制单
,A.cBusType--业务类型
,999
FROM UFDATA_999_2010.dbo.SaleBillVouch A
LEFT JOIN UFDATA_999_2010.dbo.Department B ON A.CDepCode = B.CDepCode
LEFT JOIN UFDATA_999_2010.dbo.person C ON A.cPersonCode = C.cPersonCode
WHERE cStCode = 1 AND 2009+A.SBVID NOT IN(SELECT SaleBillID From Exp_SaleBill WHERE DBSign = 999)

问题在那里

[panzervor]

没有一个靠谱的……其实关键问题是定义别名的问题，貌似必须定义表中已有的字段名比如说2009+A.SBVID AS SBVID可以，而2009+A.SBVID AS SBVID1 就不行。