“无法绑定由多个部分组成的标识符”这个怎么解决
- SQL code
USE [LHZWeb_WWW]GO/****** Object: StoredProcedure [dbo].[SelectSaleOrderByLike] Script Date: 05/29/2012 09:52:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-------------------------------------------------- Generated By: dell using CodeSmith 5.0.0.0-- Template: StoredProcedures.cst-- Procedure Name: [dbo].[SelectSaleOrderByLike]-- Date Generated: 2011年8月19日-- Alter By: Li.Lei Alter Date: 12/19/2011------------------------------------------------ALTER PROCEDURE [dbo].[SelectSaleOrderByLike]( @OrderNo varchar(20), @Email varchar(50), @SKU varchar(20), @GoodsName nvarchar(200), @OrderDateTo datetime, @OrderDateFrom datetime, @Consignee nvarchar(20), @paystatus varchar(5), @shipment nvarchar(5), @IsGiftCard char(1), @NotRmk varchar(20), @PageSize int, @Page int, @TotalNum int out )asdeclare @sql nvarchar(2500),@r_sql nvarchar(1000) = '';----------- 公用查询条件开始 -----------------------if(ISNULL(@OrderNo,'') != '') begin set @r_sql += ' and OrderNo like '''+'%' + @OrderNo +'%'+'''';endif(DATEDIFF(DAY,ISNULL(@OrderDateFrom,0),'1900-01-1 0:00:00') != 0) begin set @r_sql += ' and @OrderDateFrom <= OrderDate ';endif(DATEDIFF(DAY,ISNULL(@OrderDateTo,0),'1900-01-1 0:00:00') != 0) begin set @r_sql += ' and @OrderDateTo >= OrderDate';endif(ISNULL(@Consignee,'') != '') begin set @r_sql += ' and Consignee like '''+'%' + @Consignee +'%'+''''endif(ISNULL(@Email,'') != '') begin set @r_sql += ' and Email like '''+'%'+@Email+'%'+'''';endif(ISNULL(@paystatus,'-1') != '-1') begin set @r_sql += ' and payStatus = ' + str(@paystatus);endif(ISNULL(@shipment,'-1') != '-1') begin set @r_sql += ' and shipment = ' + @shipment;endif(ISNULL(@IsGiftCard,'') != '') begin set @r_sql += ' and IsGiftCard = ''' + @IsGiftCard+'''';end--未备注过滤条件 add by lilei on2011-10-19if (@NotRmk!='') begin if (@NotRmk='STO') begin set @r_sql += ' and Shipment=1 and ISNULL(SalesOrder.Remark,'''')='''''; end if (@NotRmk='COE') begin set @r_sql += ' and Shipment=3 and ISNULL(SalesOrder.Remark,'''')='''''; end if (@NotRmk='CODZJS') begin set @r_sql += ' and Shipment=4 and ISNULL(SalesOrder.Remark,'''')='''''; endend------------ end ------------------------------------ if (ISNULL(@SKU,'')='' and ISNULL(@GoodsName,'')='') beginset @sql = ' Select @TotalNum = count(*) from SalesOrder Where Valid = 1';set @sql += @r_sql;exec sp_executesql @sql,N'@TotalNum int output,@OrderDateFrom datetime,@OrderDateTo datetime', @TotalNum output,@OrderDateFrom,@OrderDateTo; set @sql = 'Select T2.* , (Select Name from Area Where Area.[ID]=T2.Province) ProvinceName, (Select Name FROM Area Where Area.[ID]=T2.City) CityName, (Select Name FROM Area Where Area.[ID]=T2.District) DistrictName from (Select SalesOrder.*,c.Amount CouponAmount,(ROW_NUMBER() over(Order by SalesOrder.[ID] desc)) as rn from SalesOrderleft join Coupon c on c.CouponNo=SalesOrder.CouponNo Where SalesOrder.Valid=1'set @sql += @r_sql;set @sql += ')T2'; -- Where rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize--如果@PageSize不为-1返回全部数据if (@PageSize!=-1)beginset @sql += ' Where rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize';endexec sp_executesql @sql,N'@page int,@PageSize int,@OrderDateFrom datetime,@OrderDateTo datetime', @Page,@PageSize,@OrderDateFrom,@OrderDateTo; end else begin if(ISNULL(@GoodsName,'') != '') begin set @r_sql += ' and OrderItem.GoodsName like '''+'%'+@GoodsName+'%'+'''';endif(ISNULL(@SKU,'') != '')beginset @r_sql += ' and OrderItem.SKU like '''+'%' + @SKU + '%'+'''';endset @r_sql += N' and OrderItem.Valid = 1 and SalesOrder.Valid = 1';set @sql = ' Select @TotalNum = count(*) from SalesOrder,OrderItem Where OrderItem.SalesOrderID=SalesOrder.ID';set @sql += @r_sql;exec sp_executesql @sql,N'@TotalNum int output,@OrderDateFrom datetime,@OrderDateTo datetime', @TotalNum output,@OrderDateFrom,@OrderDateTo; set @sql = ' Select T2.* , (Select Name FROM Area WHERE ID=T2.Province) ProvinceName, (Select Name FROM Area WHERE ID=T2.City) CityName, (Select Name FROM Area WHERE ID=T2.District) DistrictNamefrom ( Select T1.*,(ROW_NUMBER() over(Order by (select ID from T1) desc)) as rn from ( Select s.[ID],s.OrderNo,s.OrderDate,s.Total,s.Consignee,s.MemberName,s.Province,s.City,s.SendTime,s.MemberNote,s.Remark,s.CouponNo,c.Amount,s.District CouponAmount from SalesOrder s,OrderItem o left join Coupon c on c.CouponNo=(select CouponNo from SalesOrder) Where o.SalesOrderID=s.ID'set @sql += @r_sql;set @sql += ' )T1 )T2' -- Where rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize--如果@PageSize不为-1返回全部数据if (@PageSize!=-1)beginset @sql += ' Where rn > (@Page-1)*@PageSize and rn<=@Page*@PageSize';endexec sp_executesql @sql,N'@page int,@PageSize int,@OrderDateFrom datetime,@OrderDateTo datetime', @Page,@PageSize,@OrderDateFrom,@OrderDateTo; end--end
- SQL code
declare @OrderNo varchar(20)='', @Email varchar(50)='', @SKU varchar(20)='MS21703384110', @GoodsName nvarchar(200)='', @OrderDateTo datetime='1900/1/1 0:00:00', @OrderDateFrom datetime='1900/1/1 0:00:00', @Consignee nvarchar(20)='', @paystatus varchar(5)='-1', @shipment nvarchar(5)='-1', @IsGiftCard char(1)='0', @NotRmk varchar(20)='', @PageSize int=30, @Page int=1, @TotalNum int exec SelectSaleOrderByLike @OrderNo,@Email,@SKU,@GoodsName,@OrderDateTo,@OrderDateFrom,@Consignee,@paystatus,@shipment,@IsGiftCard, @NotRmk,@PageSize,@Page,@TotalNum output
- SQL code
消息 4104，级别 16，状态 1，第 12 行无法绑定由多个部分组成的标识符 "OrderItem.SKU"。消息 4104，级别 16，状态 1，第 12 行无法绑定由多个部分组成的标识符 "OrderItem.Valid"。消息 4104，级别 16，状态 1，第 12 行无法绑定由多个部分组成的标识符 "SalesOrder.Valid"。
[解决办法]
你看下是不这个表里面没有这个字段 是不是语句里面字段写错了
[解决办法]
检查下是否字段存在
exec sp_executesql之前print @sql
看看语句差那里
[解决办法]
太长了不想看，但是从错误提示判断，应该是字段标示出错了。你认真检查是不是每个字段都是跟对应的表的别名标示唯一对应
[解决办法]
- SQL code
Select @TotalNum = count(*) from SalesOrder as a,OrderItem as bWhere b.SalesOrderID=a.ID and IsGiftCard = '0' --这里IsGiftCard 是那个表的？？？？？and b.SKU like '%MS21703384110%' and b.Valid = 1 and a.Valid = 1
[解决办法]
- SQL code
Select @TotalNum = count(*) from SalesOrder as a,OrderItem as bWhere b.SalesOrderID=a.ID and a.IsGiftCard = '0' --这里IsGiftCard 是那个表的？？？？？and b.SKU like '%MS21703384110%' and b.Valid = 1 and a.Valid = 1--是SalesOrder 表的你就标示一下啊，不然鬼知道他哪儿的