SQL Query, getting 'Invalid Column name' error

[lauriemc]

The following sql query works fine if I leave the four commented SET statements commented out.

 

However, if I uncomment them, or just say I uncomment the first one

 

SET @StoreID = tt_StoreID

 

then I get the following message

 

Invalid column name 'tt_StoreID'

 

Why doesn't it recognize it, and how do I fix it ?

 

*---------------------------------

 

DECLARE @txnTable table (

tt_StoreID int,

tt_StoreName nvarchar (50),

tt_BatchNumber int,

tt_OpeningTime datetime,

tt_ClosingTime datetime,

tt_TransactionNumber int,

tt_Price money,

tt_Quantity float,

tt_TenderID int,

tt_TenderDesc nvarchar (25),

tt_TEID int,

tt_ItemID int,

tt_ItemLookupCode nvarchar (25),

tt_ItemDesc nvarchar (30)

)

 

INSERT @txnTable

 

SELECT Distinct dbo.Batch.StoreID,

dbo.Store.Name,

dbo.Batch.BatchNumber,

dbo.Batch.OpeningTime,

dbo.Batch.ClosingTime,

dbo.TransactionEntry.TransactionNumber,

dbo.TransactionEntry.Price,

dbo.TransactionEntry.Quantity,

dbo.view_TenderEntry_Distinct_TenderID.TenderID,

dbo.view_TenderEntry_Distinct_TenderID.Description

AS TenderDesc,

dbo.TransactionEntry.ID AS TEID,

dbo.Item.ID,

dbo.Item.ItemLookupCode,

dbo.Item.Description

 

FROM dbo.Store

INNER JOIN

dbo.Batch ON dbo.Store.ID = dbo.Batch.StoreID

INNER JOIN

dbo.[Transaction]

ON dbo.[Transaction].BatchNumber = Batch.BatchNumber

AND dbo.[Transaction].StoreID = Batch.StoreID

INNER JOIN

dbo.TransactionEntry

ON dbo.[Transaction].StoreID = dbo.TransactionEntry.StoreID

AND dbo.[Transaction].TransactionNumber = dbo.TransactionEntry.TransactionNumber

INNER JOIN

dbo.view_TenderEntry_Distinct_TenderID

ON dbo.Batch.StoreID = dbo.view_TenderEntry_Distinct_TenderID.StoreID

AND dbo.Batch.BatchNumber = dbo.view_TenderEntry_Distinct_TenderID.BatchNumber

AND dbo.TransactionEntry.TransactionNumber = dbo.view_TenderEntry_Distinct_TenderID.TransactionNumber

INNER JOIN

dbo.Item ON dbo.TransactionEntry.ItemID = dbo.Item.ID

 

WHERE Batch.BatchNumber = 28613

 

ORDER BY Batch.StoreID,

Batch.BatchNumber,

TransactionEntry.TransactionNumber,

Item.Description

 

--

 

DECLARE @StoreID int

DECLARE @BatchNumber int

DECLARE @TransactionNo int

DECLARE @ItemDesc nvarchar (30)

 

DECLARE ttCursor CURSOR FOR

SELECT tt_StoreID,

tt_StoreName,

tt_BatchNumber,

tt_OpeningTime,

tt_ClosingTime,

tt_TransactionNumber,

tt_Price,

tt_Quantity,

tt_TenderID,

tt_TenderDesc,

tt_TEID,

tt_ItemID,

tt_ItemLookupCode,

tt_ItemDesc

FROM @txnTable

OPEN ttCursor

 

FETCH NEXT from ttCursor

 

-- The four lines below are where the errors occur

 

-- SET @StoreID = tt_StoreID

-- SET @BatchNumber = tt_BatchNumber

-- SET @TransactionNo = tt_TransactionNumber

-- SET @ItemDesc = tt_ItemDesc

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT from ttCursor

END

CLOSE ttCursor

DEALLOCATE ttCursor

 

SELECT * FROM @txnTable