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?
 
tschill120198Connect With a Mentor Commented:
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
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
 
bjamesAuthor Commented:
That gives me a new error.

Invalid column name 'SQLCODE'.
0
 
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
All Courses

From novice to tech pro — start learning today.