Must declare the scalar variable "@doc"

Asked

Viewed 442 times

0

I searched a lot about this error but nothing seemed similar to what is happening to me, I created a cursor and after fetch next returns me the title error, someone could help?

declare @doc int
,@qnt int
,@estoqueantigo int
,@onhandatt int
,@icode varchar(20)
declare crs cursor
for
select * from #tmpPedidos order by itemcode
open crs
fetch next from crs into @doc,@qnt,@estoqueantigo,@onhandatt,@icode;
      insert into #tmp2(Docentry,qty,onhand,itemcode) values (@doc,@qnt,@onhandatt,@icode)
      update #tmpPedidos set Docentry=Docentry,qty=qty,onhand=@onhandatt,conta=@onhandatt-qty,itemcode=itemcode where itemcode = @icode
close crs
deallocate crs

here there are 2 temporary tables, one to perform the function and the other to receive the results, currently I can only show the first result, I will show the code of the previous parts.

create table #tmpPedidos(
Docentry int
,qty int
,onhand int
,conta int
,itemcode varchar(20)
)
--insere na tabela
insert into #tmpPedidos(Docentry,qty,onhand,conta,itemcode)
(select a.docentry,b.Quantity,c.OnHand,(c.OnHand - b.Quantity),b.ItemCode
from ORDR a 
inner join RDR1 b on a.DocEntry = b.DocEntry
inner join OITW c on b.ItemCode = c.ItemCode
where
a.DocStatus = 'o' and a.CANCELED = 'n' and c.whscode = 
CASE when BPLId = 4 then '301' 
ELSE
'401'
END)

create table #tmp2 (
Docentry int
,qty int
,onhand int
,itemcode varchar(20)
)

note that in the first chunk of code I put there is the declaration of values, the cursor is still incomplete and I am using it "in hand" by selecting and clicking fetch next to test, so I found that only the first row is inserted in table #tmp2.

Thanks in advance!

  • Hey, Douglas, work on your question. You know that when we’re helping with questions you put on the platform we try to be as assertive as possible in the answers, but it’s important know ask. My main question is, why do you need two temporary tables? What you do with the values after insert in one and update in another, being temporary tables, does not make sense. Explain better and I can help you.

  • I thought I’d seen this, I’d seen it shared how to create a cursor, temp table, etc. A note, notes your script in order to be more eligible or interpretable to the "human eye".

  • Another thing, I tested running your script, without the Insert and there is no error. Did you happen to run the script using the all-select approach and did not select the first line where you define declare @doc int?

  • Ernesto, as it is my first time using cursor and temp table did not know how to organize the data very well so I left a bit confused, but I will improve. Currently it is the Insert that should update to get the necessary result, without it the query runs normally but I need to enter the data with the account made to bring the information I need.

  • I got it here, gentlemen.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.