SQL Query 'invalid column name'

Question

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

Answer 1

You need to use

FETCH NEXT FROM ttCursor INTO @StoreID, @BatchNumber, @TransactionNo, @ItemDesc

Also the loop that iterates over the cursor currently does nothing, but that is probably because you got stopped with the "getting data into variables" part.

Edit:
while the using the INTO @variable construct is "on the right track", the code snippet shown with only this change will fail since the cursor has more row columns declared than variables referenced in the INTO clause. The simple fix is to either add variables or remove columns in the cursor's SELECT list. It is hard to be more specific for the intent of the OP is not apparent in the code (empty loop, no action/usage based on the 4 variables shown...) or the question.

Answer 2

Set @RowA = Cursor For
SELECT tt_StoreID,
tt_BatchNumber,
tt_TransactionNumber
tt_ItemDesc
FROM @txnTable

Open @RowA
Fetch Next From @RowA
Into @StoreID, @BatchNumber, @TransactionNo, @ItemDesc
    While (@@Fetch_Status=0)
        begin
     [do work]

            Fetch Next From @RowA
            Into @StoreID, @BatchNumber, @TransactionNo, @ItemDesc
        end
    Close @RowA

If you have a key for hte values and you don't need to process a loop then just do a straight query for it.

SELECT @StoreID=tt_StoreID, @BatchNumber=tt_BatchNumber, @TransactionNo=@tt_TransactionNumber, @ItemDesc=tt_ItemDesc FROM @txnTable where [yourkey]=@Key

Answer 3

First things first: Cursors are not scabale, and have a host of other problems. Move away from cursors as quickly as you can. You can do almost anything using SET based querying rather than cursors.

Having said / ranted that, here is how your cursor code should be changed:

FETCH NEXT from ttCursor INTO @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


WHILE (@@FETCH_STATUS = 0)
BEGIN

    SET @StoreID = @tt_StoreID
    SET @BatchNumber = @tt_BatchNumber
    SET @TransactionNo = @tt_TransactionNumber
    SET @ItemDesc = @tt_ItemDesc

    /* YOUR QUERY GOES HERE */

    FETCH NEXT from ttCursor INTO @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
END
CLOSE ttCursor
DEALLOCATE ttCursor