如何解决错误“无法绑定多部分标识符”。在storeprocedure中 [英] how to solve error "The multi-part identifier could not be bound." in storeprocedure

问题描述

您好b $ b

我在商店程序中遇到问题。错误是

消息4104，级别16，状态1，过程W_usp_SO_Table，第38行

无法绑定多部分标识符dbo.SVendorMaster.SVCode。 br />


插入表工作正常。但更新命令提示错误



请给我建议



Maideen



这是SP

  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER  程序 [dbo]。[W_usp_SO_Table] 
  As  
 开始 
  TRUNCATE   TABLE  [dbo]。[SOMasterTEMP] 
 
  INSERT   INTO  [dbo]。[SOMasterTEMP]（svcode，svname，mvcode，mvname，distcode，distname，rout ecode，routename，TransportM，
 area，seqno，mon，tue，wed，thu，fri，sat，sun，holi，edition，rate，svstatus，mvstatus，
 diststatus，routestatus， type ）
 
  SELECT  dbo.SVendorMaster.SVCode，dbo.SVendorMaster.SVName，dbo.SVendorMaster .MVCode，dbo.MVendorMaster.MVName，
 dbo.SVendorMaster.DistCode，dbo.DistributorMaster.DistName，dbo.DistributorMaster.RouteCode，dbo.RouteMaster.RouteDesc，
 dbo.RouteMaster.TransporterM，dbo.RouteMaster .Area，dbo.SVendorMaster.SeqNo，dbo.SVendorMaster.Mon，dbo.SVendorMaster.Tue，
 dbo.SVendorMaster.Wed，dbo.SVendorMaster.Thu，dbo.SVendorMaster.Fri，dbo.SVendorMaster.Sat，dbo .SVendorMaster.Sun，
 dbo.SVendorMaster.Holi，dbo.MVendorMaster.Edition，dbo.MVendorMaster.Rate，dbo.SVendorMaster.AStatus  AS  SVStatus，
 dbo.MVendorMaster.AStatus  AS  MVStatus，dbo.DistributorMaster.Astatus  AS  DistStatus，dbo.RouteMaster.AStatus  AS  RouteStatus，
 dbo.MVendorMaster。类型 
  FROM  dbo.RouteMaster < span class =code-keyword> INNER   JOIN  
 dbo.DistributorMaster  ON  dbo.RouteMaster.RouteCode = dbo.DistributorMaster.RouteCode  INNER   JOIN  
 dbo.SVendorMaster < span class =code-keyword> INNER   JOIN  
 dbo.MVendorMaster  ON  dbo.SVendorMaster.MVCode = dbo.MVendorMaster.MVCode  ON  
 dbo.DistributorMaster.DistCode = dbo.SVendorMaster.DistCode 
  WHERE （db o.SendorMaster.AStatus = N '  A'） AND （dbo.MVendorMaster.AStatus = N '  A'）
  AND （dbo.DistributorMaster.Astatus = N '  A'） AND （dbo.RouteMaster.AStatus = N '  A'）
 
 
  / *   update qty based on天* /  
 
 声明  @ DOW   NVARCHAR （ 20 ）
 
  SET  @ Dow = '  SELECT DATEName（dw，GETDATE（））as DaysName' 
 
  SELECT  dbo.SVendorMaster.svcode，dbo.SVendorMaster.MON，dbo.SVendorMaster.TUE，dbo.SVendorMaster.WED，dbo.SVendorMaster.THU，
 dbo.SVendorMaster .FRI，dbo.SVendorMaster.SAT，dbo.SVendorMaster.SUN，dbo.SVendorMaster.HOLI 
  FROM  [dbo]。[SVendorMaster]  INNER   JOIN  dbo.SOMasterTEMP  ON  dbo.SVendorMaster.svcode = dbo.SOMasterTEMP.svcode 
 
 
  IF   @ DOW  = < span class =code-string>'  Monday' 
  UPDATE  [ dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.Mon  WHERE  dbo.SOMasterTEMP.svcode = dbo .SVendorMaster.SVCode 
  ELSE   IF   @ DOW  = ' 星期二' 
  UPDATE  [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.Tue  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
  ELSE   IF  < span class =code-sdkkeyword> @ DOW  = '  Wednesday' 
 更新 [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.wed  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
  ELSE   IF   @ DOW  = '  Thursday' 
 更新 [dbo]。[ SOMasterTEMP]  SET  qty = dbo.SVendorMaster.thu  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
  ELSE   IF   @ DOW  = '  Friday' 
  UPDATE  [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.fri  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
  ELSE   IF   @DOW  = ' 星期六' 
  UPDATE  [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.sat  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorM aster.SVCode 
  ELSE   IF   @ DOW  = ' 星期日' 
 更新 [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.sun  WHERE  dbo。 SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
  ELSE   IF   @ DOW  = '  Holi' 
  UPDATE  [dbo]。[SOMasterTEMP]  SET  qty = dbo.SVendorMaster.holi  WHERE  dbo.SOMasterTEMP.svcode = dbo.SVendorMaster.SVCode 
 
 
 
  END  

解决方案

你正在更新si同时加入两张桌子。

所以这就是你可以尝试的事情

 UPDATE [dbo]。[SOMasterTEMP] SET qty = dbo.SVendorMaster.Mon< br /> 
  FROM dbo.SOMasterTEMP，dbo.SVendorMaster < br /> 
 WHERE dbo.SOMasterTEMP.svcode = dbo。 SVendorMaster.SVCode 

Hi
I have an issue in store procedure. Error is
Msg 4104, Level 16, State 1, Procedure W_usp_SO_Table, Line 38
The multi-part identifier "dbo.SVendorMaster.SVCode" could not be bound.

Insert table is working fine. But update command is prompt the error

Pls advice me

Maideen

Here is SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[W_usp_SO_Table]
As
Begin
TRUNCATE TABLE [dbo].[SOMasterTEMP]

INSERT INTO [dbo].[SOMasterTEMP] ( svcode, svname, mvcode, mvname, distcode, distname, routecode, routename, TransportM, 
                                  area, seqno, mon, tue, wed, thu, fri, sat, sun, holi, edition, rate, svstatus, mvstatus, 
								  diststatus, routestatus, type)

SELECT      dbo.SVendorMaster.SVCode, dbo.SVendorMaster.SVName, dbo.SVendorMaster.MVCode, dbo.MVendorMaster.MVName, 
			dbo.SVendorMaster.DistCode, dbo.DistributorMaster.DistName, dbo.DistributorMaster.RouteCode, dbo.RouteMaster.RouteDesc, 
			dbo.RouteMaster.TransporterM, dbo.RouteMaster.Area, dbo.SVendorMaster.SeqNo, dbo.SVendorMaster.Mon, dbo.SVendorMaster.Tue, 
			dbo.SVendorMaster.Wed, dbo.SVendorMaster.Thu, dbo.SVendorMaster.Fri,dbo.SVendorMaster.Sat, dbo.SVendorMaster.Sun, 
			dbo.SVendorMaster.Holi, dbo.MVendorMaster.Edition, dbo.MVendorMaster.Rate,dbo.SVendorMaster.AStatus AS SVStatus, 
			dbo.MVendorMaster.AStatus AS MVStatus, dbo.DistributorMaster.Astatus AS DistStatus,dbo.RouteMaster.AStatus AS RouteStatus, 
			dbo.MVendorMaster.Type
FROM        dbo.RouteMaster INNER JOIN
            dbo.DistributorMaster ON dbo.RouteMaster.RouteCode = dbo.DistributorMaster.RouteCode INNER JOIN
            dbo.SVendorMaster INNER JOIN
            dbo.MVendorMaster ON dbo.SVendorMaster.MVCode = dbo.MVendorMaster.MVCode ON 
			dbo.DistributorMaster.DistCode = dbo.SVendorMaster.DistCode
WHERE       (dbo.SVendorMaster.AStatus = N'A') AND (dbo.MVendorMaster.AStatus = N'A') 
		    AND (dbo.DistributorMaster.Astatus = N'A') AND (dbo.RouteMaster.AStatus = N'A')


/*  update qty based on days*/

Declare @DOW NVARCHAR(20)

SET @Dow='SELECT DATEName(dw,GETDATE()) as DaysName'

SELECT dbo.SVendorMaster.svcode, dbo.SVendorMaster.MON,dbo.SVendorMaster.TUE,dbo.SVendorMaster.WED,dbo.SVendorMaster.THU,
	   dbo.SVendorMaster.FRI, dbo.SVendorMaster.SAT,dbo.SVendorMaster.SUN,dbo.SVendorMaster.HOLI 
	   FROM [dbo].[SVendorMaster] INNER JOIN dbo.SOMasterTEMP ON dbo.SVendorMaster.svcode=dbo.SOMasterTEMP.svcode


IF @DOW ='Monday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Mon WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW ='Tuesday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Tue WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Wednesday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.wed WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW  = 'Thursday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.thu WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW ='Friday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.fri WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Saturday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.sat WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Sunday'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.sun WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode
ELSE IF @DOW = 'Holi'
	UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.holi WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode



END

解决方案

You are updating while simultaneously joining two tables.
So this is what you could try

UPDATE [dbo].[SOMasterTEMP] SET qty= dbo.SVendorMaster.Mon <br />
FROM dbo.SOMasterTEMP,dbo.SVendorMaster  <br />
WHERE dbo.SOMasterTEMP.svcode=dbo.SVendorMaster.SVCode

这篇关于如何解决错误“无法绑定多部分标识符”。在storeprocedure中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！