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

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

 

 

 

 

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;

 

        [OrderID] INT IDENTITY(1,1) NOT NULL,

        [OrderDate] DATE,

        [PackORTrial] NVARCHAR(5) NULL,

        [MonthlyDaily] NVARCHAR(255) NULL,

        [Quantity] INT,

        [Supplier] NVARCHAR(50),

        [Brand] NVARCHAR(50),

    )

 

 

    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'

    SELECT TOP 1 [OrderID] = @SelectedOrderID, CONVERT(VARCHAR(10), oc.OrderDate, 103) AS OrderDate, SupplierID, BrandID,

        oc.PACKorTRIAL, oc.MONTHLYDAILY, oc.QUANTITY

    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;

    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