Stored Procedure Update not working after Select INTO temptable - please help if you can

Copper Contributor
Hi All
 
I am wanting to update 2 fields in a record I insert into a temporary table.
I get this error on execute
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Supplier'.
I have triple checked the fields, datatype and sizes are the same in the actual tables and the temporary table #tmpClientOrdersCL
 
Any assistance appreciated.
Many thanks,
Myles Morris
 
 
 
USE [LINCOLN]
GO
/****** Object:  StoredProcedure [dbo].[GetClientLastOrderCL]    Script Date: 24/10/2023 10:40:15 am ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetClientLastOrderCL]
@ClientID INT,
@EyeFilter NVARCHAR(2)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;
 
CREATE TABLE #tmpClientOrdersCL (
        [OrderID] INT IDENTITY(1,1) NOT NULL,
        [OrderDate] DATE,
        [PackORTrial] NVARCHAR(5) NULL,
        [MonthlyDaily] NVARCHAR(255) NULL,
        [Quantity] INT,
       [SupplierID] INT,
       [BrandID] INT,
        [Supplier] NVARCHAR(50),
        [Brand] NVARCHAR(50),
    )
 
 
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'
DECLARE @SelectedOrderID INT
    SELECT TOP 1 [OrderID] = @SelectedOrderID, CONVERT(VARCHAR(10), oc.OrderDate, 103) AS OrderDate, SupplierID, BrandID,
        oc.PACKorTRIAL, oc.MONTHLYDAILY, oc.QUANTITY
INTO #tmpClientOrdersCL
    FROM tblOrdersCL AS oc
    INNER JOIN tblClients AS c ON c.ClientID = oc.ClientID
    WHERE c.ClientID = @ClientID AND oc.EYE = ' + CHAR(39) + @EyeFilter + CHAR(39) + ';
    UPDATE t
    SET t.Supplier = s.SupplierName
    FROM #tmpClientOrdersCL AS t JOIN tblSuppliers AS s ON t.SupplierID = s.SupplierID
    WHERE t.OrderID = @SelectedOrderID;
UPDATE t
    SET t.Brand = b.BrandName
    FROM #tmpClientOrdersCL AS t JOIN tblSupplierBrands AS b ON t.BrandID = b.BrandID
    WHERE t.OrderID = @SelectedOrderID;
     
    SELECT * FROM #tmpClientOrdersCL;
 
    ';
 
    PRINT @SQL;
 
    EXEC sp_executesql @SQL, N'@ClientID INT, @EyeFilter NVARCHAR(2)', @ClientID, @EyeFilter;
 
DROP TABLE #tmpClientOrdersCL;
END

 

0 Replies