Checking whether account is valid or not

[Dudek]

Was wondering whether anyone can help me with this..

I have two tables A and B and one of the columns is an account_id. What i need to do is to check whether the account_id in table B is in table A. If it is, update a column called status to ACTIVE. If the account isnt in table A, then update to INVALID.

I have created a stored proc but it doesnt work.. Heres the code..


create proc check as
begin
declare csr cursor for
select @account_id =a.account_id, @ac_id=b.account_id
from tableA a, tableB b
where a.account_id = b.account_id

open csr
while (@@sqlstatus !=2)
begin
if @account_id = NULL
begin
update tableB set status = 'INVALID'
where account_id=@ac_id
end

else
begin
update tableA set status = 'VALID'
where account_id=@ac_id
end

end

close csr
deallocate cursor csr

end

I suspect this is not workign cos the cursor isnt returning a resultset when there is no match(i.e no such account) .It will only return a resultset if there is such a account. So, can anybody give me an alternative here?

TIA
JD

 

[Tim1]

Hiya,

Why don't you try checking @@rowcount to determine if a valid account number has been found, and just running the cursor through table b as below:

create proc check as
begin
declare csr cursor for
select @ac_id=b.account_id
from tableB b


open csr
while (@@sqlstatus !=2)
begin
SELECT @account_id = account_id
FROM tableA
WHERE account_id = @ac_id

IF @@rowcount = 0
begin
update tableB set status = 'INVALID'
where account_id=@ac_id
end

else
begin
update tableA set status = 'VALID'
where account_id=@ac_id
end

end

close csr
deallocate cursor csr

end

May need a little tweaking, but hopefully should do the job for you.

Tim

 

[ETasse]

Wouldn't a simple outer join do the job ?

UPDATE  a,b
SET     a.status = "VALID"
WHERE   a.account_id = b.account_id
 AND    a.account_id IS NOT NULL

---

UPDATE  a,b
SET     a.status = "INVALID"
WHERE   a.account_id *= b.account_id
 AND    b.account_id IS NULL