SQL Server Update Table variable from another table Error: Must declare the scalar variable

Last Reply 7 months ago By dharmendr

Posted 7 months ago

Hi

I have in issue in update @temp table column from another table column value.

Once run Stored procedure, below is error

" Msg 137, Level 16, State 1, Procedure usp_Report_Statement, Line 43 [Batch Start Line 7] Must declare the scalar variable "@Statement".

Pls advice me

Thank  you in advance

Maideen

Below is my code

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[usp_Report_Statement]
	@Action VARCHAR(50)=NULL,@sid varchar(25) = NULL,@nric varchar(30) = NULL,@NAME VARCHAR(100) NULL
AS
BEGIN

SET NOCOUNT ON;
   DECLARE @Statement TABLE
    (
		[id] [bigint] IDENTITY(1,1) NOT NULL,
		[RCNO] VARCHAR(10) NULL,
		[RCDATE] DATE NULL,
		[SID] [varchar](25) NULL,
		[NAME] [varchar](50) NULL,
		[NRIC] [varchar](25) NULL,
		[COURSECODE] [varchar](20) NULL,
		[COURSEFEEORI] [varchar](10) NULL,
		[AMOUNT] NUMERIC (18,2) NULL,
		[MODE] VARCHAR(20) NULL,
		[RCVDFOR] VARCHAR(50) NULL,
		[CHQAMT] NUMERIC(18,2) NULL,
		[STATUS] VARCHAR(15) NULL,
		[LOCATION] VARCHAR(10) NULL,
		[TAGID] VARCHAR(5) NULL,
		[INTAKEM] VARCHAR(25) NULL,
		[INTAKEY] VARCHAR(5) NULL

    )
		INSERT INTO @Statement (RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) 
		SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
		FROM [dbo].[US_ReceiptDetails] where [sid]=@sid AND [NAME] = @NAME 

		INSERT INTO @Statement (RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID) 
		SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
		FROM [dbo].[US_RefundDetails] where [sid]=@sid AND [NAME] = @NAME

		UPDATE @Statement SET INTAKEM = A.INTAKEM, INTAKEY = A.INTAKEY FROM [dbo].[AD_StudentRecord] A
		WHERE @Statement.SID = A.SID

		SELECT * FROM @Statement
	END

 

You are viewing reply posted by: dharmendr 7 months ago.
Posted 7 months ago

Hi maideen,

For this you have two option. 

1. Either you have to change the column name SID of Variable table with different name. So that its not going to Ambiguous.

ALTER PROCEDURE [dbo].[usp_Report_Statement]
    @Action VARCHAR(50)=NULL,@sid varchar(25) = NULL,@nric varchar(30) = NULL,@NAME VARCHAR(100) NULL
AS
BEGIN
  
    SET NOCOUNT ON;
    DECLARE @Statement TABLE
    (
        [id] [bigint] IDENTITY(1,1) NOT NULL,
        [RCNO] VARCHAR(10) NULL,
        [RCDATE] DATE NULL,
        [StID] [varchar](25) NULL, -- Changed with different name.
        [NAME] [varchar](50) NULL,
        [NRIC] [varchar](25) NULL,
        [COURSECODE] [varchar](20) NULL,
        [COURSEFEEORI] [varchar](10) NULL,
        [AMOUNT] NUMERIC (18,2) NULL,
        [MODE] VARCHAR(20) NULL,
        [RCVDFOR] VARCHAR(50) NULL,
        [CHQAMT] NUMERIC(18,2) NULL,
        [STATUS] VARCHAR(15) NULL,
        [LOCATION] VARCHAR(10) NULL,
        [TAGID] VARCHAR(5) NULL,
        [INTAKEM] VARCHAR(25) NULL,
        [INTAKEY] VARCHAR(5) NULL
  
    )
    INSERT INTO @Statement (RCNO,RCDATE,StID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID)
    SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
    FROM [dbo].[US_ReceiptDetails] where [sid]=@sid AND [NAME] = @NAME
  
    INSERT INTO @Statement (RCNO,RCDATE,StID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,TAGID)
    SELECT RCNO,RCDATE,SID,NAME,COURSECODE,AMOUNT,RCVDFOR,STATUS,LOCATION,'A'
    FROM [dbo].[US_RefundDetails] where [sid]=@sid AND [NAME] = @NAME
  
    UPDATE @Statement SET INTAKEM = A.INTAKEM, INTAKEY = A.INTAKEY FROM [dbo].[AD_StudentRecord] A
    WHERE StID = A.SID
  
    SELECT * FROM @Statement
END

2. Use Temp Table so that you can call column name with table name.

Ex:

CREATE TABLE #Statement
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [CustomerId] INT NULL,
    [Name] [varchar](25) NULL,
    [Country] [varchar](25) NULL
)

INSERT INTO #Statement (CustomerId,Name,Country)
SELECT CustomerId,Name,Country
FROM [dbo].[Customers] 
  
UPDATE #Statement 
SET Country = A.Country 
FROM [dbo].[CustomerTest] A
WHERE #Statement.CustomerId = A.CustomerId

SELECT * FROM #Statement
DROP TABLE #Statement