SQLCODE

What is wrong with this syntax? I've written this in Query Analyzer (7.0 beta) and when I attempt to run it I get an "Incorrect syntax near '='." error.

--My Code
declare cProcList cursor for
      select name from sysobjects
            where xtype = 'P'
                  and name > 'du'
            order by name
open cProcList
while (SQLCODE == 0)
(
      fetch cProcList
)
close cProcList
bjamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Victor SpiridonovCommented:
>while (SQLCODE == 0)
Why do you have two 'equal' signs?
0
 
bjamesAuthor Commented:
That is the way the example until FETCH in Books On-Line shows it. And when I change the == to a single = I get another error:

Incorrect syntax near the keyword 'fetch'.

0
 
tschill120198Commented:
I don't have the SQL 7 BOL with me right now, but the parenthesis around the fetch look suspicious.  I've not spent a lot of time with SQL 7, so maybe they've added that syntax... try begin/end instead:

while (SQLCODE = 0)
begin
    fetch cProcList
end
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bjamesAuthor Commented:
That gives me a new error.

Invalid column name 'SQLCODE'.
0
 
tschill120198Commented:
Let's back up a bit... what is it you are trying to do?  

Looks to me like you want to cursor through all sprocs and do something with each... try this:

    declare @sprocname varchar(128)
    declare cProcList cursor for
        select  name
        from    sysobjects
        where   xtype = 'p'
        and     name > 'du'
        order by name
    open cProcList
    while (1=1) begin
        fetch next from cProcList into @sprocname
        if @@fetch_status = -1 break
        if @@fetch_status = -2 continue
        if @@fetch_status <> 0 break
        -- do whatever here with each sproc
        print @sprocname
    end
    close cProcList



0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
bjamesAuthor Commented:
Thanks. It works great. Sorry for the trouble. I'm still new to SQL Server.
0
 
tschill120198Commented:
No trouble at all.  Enjoy SQL 7!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.