Member Avatar for Maideen

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
  • 2 Contributors
  • 1 Reply
  • 717 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by hiren2927

Dani AI

Generated

The error means SQL Server cannot resolve the table/column you referenced in that UPDATE. In this procedure that happens for two reasons: the weekday variable @DOW is being set to the literal text "SELECT DATEName(...)" instead of the function result, and the UPDATE statements reference columns from SVendorMaster without bringing that table into scope for the UPDATE. was on the right track by using an UPDATE...FROM, but the variable and the update logic still need fixing.

Fix approach — compute the current day correctly, then do one joined UPDATE that maps the day to the right vendor column. This keeps everything in scope and avoids multiple UPDATEs:

DECLARE @DOW NVARCHAR(20);
SET @DOW = DATENAME(weekday, GETDATE());

UPDATE t
SET qty =
  CASE @DOW
    WHEN 'Monday'    THEN s.Mon
    WHEN 'Tuesday'   THEN s.Tue
    WHEN 'Wednesday' THEN s.Wed
    WHEN 'Thursday'  THEN s.Thu
    WHEN 'Friday'    THEN s.Fri
    WHEN 'Saturday'  THEN s.Sat
    WHEN 'Sunday'    THEN s.Sun
    ELSE s.Holi
  END
FROM dbo.SOMasterTEMP t
JOIN dbo.SVendorMaster s ON t.svcode = s.SVCode;

Extra notes: DATENAME returns language-specific names, so if the server language/collation differs use DATEPART(weekday,GETDATE()) and map the numeric values (or set DATEFIRST explicitly). If "Holi" is a business holiday flag rather than an actual weekday, handle it with a holiday/calendar table or a vendor flag rather than expecting DATENAME to return it. Also watch collation/case-sensitivity for column names and test the join with a SELECT first to verify values before updating.

Member Avatar for hiren2927

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.