CURSOR_STATUS question
in [Programming SQL]
|
Prev: Possible to do a partial join - for partially matching join fi
Next: Query Question - Friend - Friend - Friend - Friend ... relationshi
From: Keith G Hicks on 27 May 2010 19:49 I was having trouble with this earlier, thought I solved it but then trouble again. I want to create the following: DECLARE crBadAdjs INSENSITIVE CURSOR FOR..... But I need to test it for CURSOR_STATUS which I don't seem to be able to do with INSENSITIVE because I don't know if it's local or global.. If I do the following, it works: IF CURSOR_STATUS('local', 'crBadAdjs') > 0 BEGIN CLOSE crBadAdjs DEALLOCATE crBadAdjs END DECLARE crBadAdjs CURSOR LOCAL FOR .... But if I use INSENSITIVE like this: DECLARE crBadAdjs INSENSITIVE CURSOR LOCAL FOR .... it won't compile. If I do this: IF CURSOR_STATUS('local', 'crBadAdjs') > 0 BEGIN CLOSE crBadAdjs DEALLOCATE crBadAdjs END DECLARE crBadAdjs INSENSITIVE CURSOR FOR .... I get errors when I run it that the cursor does not exist or already exists. The cursor is local in that I only need it for the stored procedure it's running in. But is this local, global or what? Nothing I do don't seem to work (to quote the Stones): DECLARE crBadAdjs INSENSITIVE CURSOR FOR ....
From: Erland Sommarskog on 28 May 2010 03:04 Keith G Hicks (krh(a)comcast.net) writes: > I want to create the following: > > DECLARE crBadAdjs INSENSITIVE CURSOR FOR..... > > But I need to test it for CURSOR_STATUS which I don't seem to be able to > do with INSENSITIVE because I don't know if it's local or global.. It's global ...unless the database option to make local cursors to be the default is in force. Life is fun, isn't it? > But if I use INSENSITIVE like this: > > DECLARE crBadAdjs INSENSITIVE CURSOR LOCAL FOR .... > > it won't compile. INSENSITIVE is ANSI-compliant, but unfortunately you cannot combine it with LOCAL/GLOBAL. So the easy way out is to use proprieatry syntax: DECLARE cur CURSOR STATIC LOCAL FOR As far as I can make out STATIC and INSENSITIVE are the same. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Keith G Hicks on 28 May 2010 08:13
That works. Thanks. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D865C58720C5Yazorman(a)127.0.0.1... > Keith G Hicks (krh(a)comcast.net) writes: >> I want to create the following: >> >> DECLARE crBadAdjs INSENSITIVE CURSOR FOR..... >> >> But I need to test it for CURSOR_STATUS which I don't seem to be able to >> do with INSENSITIVE because I don't know if it's local or global.. > > It's global ...unless the database option to make local cursors to be > the default is in force. Life is fun, isn't it? > >> But if I use INSENSITIVE like this: >> >> DECLARE crBadAdjs INSENSITIVE CURSOR LOCAL FOR .... >> >> it won't compile. > > INSENSITIVE is ANSI-compliant, but unfortunately you cannot combine it > with LOCAL/GLOBAL. So the easy way out is to use proprieatry syntax: > > DECLARE cur CURSOR STATIC LOCAL FOR > > As far as I can make out STATIC and INSENSITIVE are the same. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |