Must declare the scalar variable "@invoice_ID"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable "@invoice_ID"

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-04-26 : 09:34:40

I'm trying to run the one off code below to update a few records but get the above error. Here's the code, can you see what's wrong?


DECLARE @invoice_ID int
DECLARE db_cursor CURSOR FOR  
select invoices.id
from income inner join invoices on income.invoiceid = invoices.id
where isdate(invoices.paid_date) = 0 and on_bank_statement=1
OPEN db_cursor  
FETCH NEXT FROM db_cursor
INTO @invoice_ID
WHILE @@FETCH_STATUS = 0  
BEGIN  
update income set on_bank_statement=0 where invoiceid = @invoice_ID
go
FETCH NEXT FROM db_cursor
INTO @invoice_ID
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 10:59:56

Your @invoice_ID is not known after you type a "go".
Take away that "go".

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-04-26 : 11:03:15

Excellent, thanks for that!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-26 : 11:06:13

Why not just do:

UPDATE inc SET on_bank_statement=0
FROM income inc
INNER JOIN invoices inv on inc.invoiceid=inv.id
where isdate(inv.paid_date)=0 and inc.on_bank_statement=1

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-04-26 : 11:14:50

I wasn't sure of the update syntax with inner join in so I found a cursor example and tweaked it.

I only needed to run it the once

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources