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
