Problem with running a while loop in T-SQL
-
Hi,
This is a snippet of the code that is giving me a problem, when i run this code I get the following error message:
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 84
Incorrect syntax near ';'.
Can anybody help?
_______________________________________________________
declare c cursor
for
select [id], max([order])
from #a
group by [id]
order by max([order]) asc;
open c;
declare @id int;
DECLARE @DelCmd nvarchar(1000);
--begin tran
FETCH NEXT FROM c INTO @id, @Order;
WHILE (@@fetch_status = 0)
BEGIN
FETCH NEXT FROM c INTO @id, @Order; <-Line 83
END; <-Line 84
CLOSE c;
DEALLOCATE c;
-
The first thing I would try is getting rid of the semicolons. I don't think they're strictly necessary.
John
-
I have removed the semi colons and i still get the same error, except the error for line 84 has moved to Line 86:
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near ' '.
Msg 156, Level 15, State 1, Line 86
Incorrect syntax near the keyword 'CLOSE'.
-
Have you posted all of your code? If so, what is it supposed to do? It fetches the results into the variables, but then moves on without actually doing anything with them.
John
-
Hi,
This is the entire code, i removed what it was doing in the while to test if it would work without any errors.
----------------------------------------------------------------------
set nocount on
declare @Order int
Set @Order = 1
--Checks if the Temp table #a exists and if it does the table is dropped
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#a'))
DROP TABLE #a
--Checks if the Temp table #b exists and if it does the table is dropped
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#b'))
DROP TABLE #b
create table #a ([id] int, [order] int)
create table #b (fid int, rid int)
-- First, delete from tables which are not referenced by any foreign key
insert into #a
select id, 0
from sysobjects left join sysforeignkeys on id = rkeyid
where objectproperty(id, 'isusertable') = 1
and rkeyid is null
-- Make a clone of key pairs from sysforeignkeys
insert into #b
select distinct fkeyid, rkeyid
from sysforeignkeys
-- While there are leaves tables do:
while exists(select distinct f1.fid
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null)
begin
-- Insert leaves first
insert into #a
select distinct f1.fid, @Order
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
-- Delete just "ordered" tables
delete f1
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
Set @Order = @Order + 1
end
-- Insert if something is rest in #b (e.g. self-join)
insert into #a
select distinct fid, @Order
from #b
-- Insert top level tables
insert into #a
select distinct f1.rkeyid, @Order + 1
from sysforeignkeys f1 left join sysforeignkeys f2 on f1.rkeyid = f2.fkeyid
where f2.rkeyid is null
-- Now when we have all tables in temporary table #a
-- we can create dynamic script and execute it
-- If you need list like this often you can create table-valued function
-- starting code from this line, replacing temp table with function call
declare c cursor
for
select [id], max([order])
from #a
group by [id]
order by max([order]) asc
open c
declare @id int
DECLARE @DelCmd nvarchar(1000)
FETCH NEXT FROM c INTO @id, @Order
WHILE (@@fetch_status = 0)
BEGIN
????IF (@@fetch_status <> -2)
????BEGIN??????
????????SET @DelCmd = 'DELETE FROM ' + quotename(Object_name(@id));
????????PRINT cast(quotename(Object_name(@id)) as char(50)) + cast(@@rowcount as char(7)) + ' row(s) deleted ';
????END;
????FETCH NEXT FROM c INTO @id, @Order
END
CLOSE c
DEALLOCATE c
drop table #a
drop table #b
set nocount off
-
select [id], max([order])
from #a
group by [id]
order by max([order]) asc
The above T-SQL statement is wrong. You required Group By clause as you are using MAX function. The statement will return only one record.
-
Anirban Paul (4/18/2008)
select [id], max([order])from #a
group by [id]
order by max([order]) asc
The above T-SQL statement is wrong. You required Group By clause as you are using MAX function. The statement will return only one record.
Hnnh? On my database, it returned seven rows.
Anyway, I think the problem is with those funny characters in your BEGIN...END block. You don't actually need the inner block, because you've already checked @@FETCH_STATUS is 0 so it can't be -2, so get rid of that. If you then remove the indentations in your code, it should parse correctly.
John
-
Thanks John that worked
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply