Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cursor in a dynamic SQL

8 posts in Windows NT Last posting was on 2001-03-08 09:05:04.0Z
Ramesh Posted on 2001-02-27 10:24:08.0Z
From: Ramesh
Date: Tue, 27 Feb 2001 05:24:08 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Cursor in a dynamic SQL
Message-ID: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums>
Lines: 15
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1177
Article PK: 1088635

Hi,


How to use cursors within a dynamic SQL:

exec("declare @a int declare cursor_name cursor for select * from
tablename .....")

This gives error as : DECLARE CURSOR must be the only statement in a query
batch.

Is there any work around for this ?

Thanks
Ramesh


Bret Halford Posted on 2001-02-28 00:25:31.0Z
Message-ID: <3A9C457B.4E3E6E2C@sybase.com>
Date: Tue, 27 Feb 2001 17:25:31 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Cursor in a dynamic SQL
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 19
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1175
Article PK: 1088633

This is expected, see the ASE 12.0 Reference Manual, Vol 2, "Execute", section
"Dynamically Executing Transact-SQL", second bullet: "You cannot... execute
the following commands:... declare cursor..."

I don't know of any workarounds, however I don't see anything in your example
that requires
dynamic execution either. I more detailed example of what you are trying to do
may suggest
some workarounds.


-bret

Ramesh wrote:

> Hi,
>
> How to use cursors within a dynamic SQL:
>
> exec("declare @a int declare cursor_name cursor for select * from
> tablename .....")
>
> This gives error as : DECLARE CURSOR must be the only statement in a query
> batch.
>
> Is there any work around for this ?
>
> Thanks
> Ramesh


Ramesh Posted on 2001-02-28 07:09:31.0Z
From: Ramesh
Date: Wed, 28 Feb 2001 02:09:31 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Cursor in a dynamic SQL
Message-ID: <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums>
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com>
Lines: 18
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1173
Article PK: 1088630

Thankyou Bret.

The problem on hand is this :

execute ( " declare @var char(10) declare cur1 cursor for "+
" select name from MyTable "+
" open cur1 "+
" fetch cur1 into @var "+
" while @@sqlstatus = 0 "+
" begin "+
" update Table2 set id = 10 where name = "+@var +
" fetch cur1 into @var "
" End "+
" deallocate cursor cur1 ")


Bret Halford Posted on 2001-02-28 17:40:15.0Z
Message-ID: <3A9D37FF.4040E319@sybase.com>
Date: Wed, 28 Feb 2001 10:40:15 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Cursor in a dynamic SQL
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com> <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 17
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1169
Article PK: 1088624


Ramesh wrote:

> Thankyou Bret.
>
> The problem on hand is this :
>
>

Perhaps this, it doesn't require any dynamic sql at all...

create table MyTable (name char(10))
go
insert MyTable values ("a")
insert MyTable values ("b")
go
create table Table2 (id int, name char(10))
go
insert Table2 values (1,"a")
insert Table2 values (1,"c")
go
declare cur1 cursor for select name from MyTable
go
declare @var char(10)
open cur1
fetch cur1 into @var
while @@sqlstatus = 0
begin
update Table2 set id = 10 where name = @var
fetch cur1 into @var
End
deallocate cursor cur1
go


Ramesh Posted on 2001-03-01 07:30:32.0Z
From: Ramesh
Date: Thu, 1 Mar 2001 02:30:32 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Cursor in a dynamic SQL
Message-ID: <65A31CFA1B40F78600293F5A85256A02.0062F11B85256A01@webforums>
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com> <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums> <3A9D37FF.4040E319@sybase.com>
Lines: 23
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1166
Article PK: 1088617

I am very sorry Bret. I missed two most important things(for which I gave
this query in the first place)

1. @database is passed as a parameter
2. This cursor is in a stored procedure.

execute ( " declare @var char(10) declare cur1 cursor for "+
" select name from "+@database+"..MyTable "+
" open cur1 "+
" fetch cur1 into @var "+
" while @@sqlstatus = 0 "+
" begin "+
" update "+@database+"..Table2 set id = 10 "+
" where name = "+@var +
" fetch cur1 into @var "
" End "+
" deallocate cursor cur1 ")


Bret Halford Posted on 2001-03-06 21:06:54.0Z
Message-ID: <3AA5516E.50640CF5@sybase.com>
Date: Tue, 06 Mar 2001 14:06:54 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Cursor in a dynamic SQL
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com> <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums> <3A9D37FF.4040E319@sybase.com> <65A31CFA1B40F78600293F5A85256A02.0062F11B85256A01@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 20
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1147
Article PK: 1088623

Now you're talking! I don't see any workaround for this at the moment.

-bret

Ramesh wrote:

> I am very sorry Bret. I missed two most important things(for which I gave
> this query in the first place)
>
> 1. @database is passed as a parameter
> 2. This cursor is in a stored procedure.
>
> execute ( " declare @var char(10) declare cur1 cursor for "+
> " select name from "+@database+"..MyTable "+
> " open cur1 "+
> " fetch cur1 into @var "+
> " while @@sqlstatus = 0 "+
> " begin "+
> " update "+@database+"..Table2 set id = 10 "+
> " where name = "+@var +
> " fetch cur1 into @var "
> " End "+
> " deallocate cursor cur1 ")


drana Posted on 2001-03-07 21:12:15.0Z
From: drana
Date: Wed, 7 Mar 2001 16:12:15 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Cursor in a dynamic SQL
Message-ID: <C8B4C45A177E081600747A6D85256A08.007696E385256A07@webforums>
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com> <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums> <3A9D37FF.4040E319@sybase.com> <65A31CFA1B40F78600293F5A85256A02.0062F11B85256A01@webforums> <3AA5516E.50640CF5@sybase.com>
Lines: 26
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1144
Article PK: 1088692

Another Alternative without dynamic SQL. Create in sybsystemprocs
and name it sp_%, run it with exec @var. Don't mention the database name
inside the stored proc.

-------------------------------------
use sybsystemprocs
go

create proc sp_test_proc(@var char(10))
declare cur1 cursor for
select name from MyTable
open cur1
fetch cur1 into @var
while @@sqlstatus = 0
begin
update Table2 set id = 10
where name @var
fetch cur1 into @var
End
deallocate cursor cur1
go

declare @exec_str varchar(255),@db_name varchar(30)
select @db_name="YOUR_DB"
select @exec_str=@db_name+"..sp_test_proc @var"
exec @exec_str


Ramesh Posted on 2001-03-08 09:05:04.0Z
From: Ramesh
Date: Thu, 8 Mar 2001 04:05:04 -0500
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Cursor in a dynamic SQL
Message-ID: <DA482E07815D00740031E71585256A09.0077DEFF85256A08@webforums>
References: <11C0D730A12755540039243A85256A00.0039244985256A00@webforums> <3A9C457B.4E3E6E2C@sybase.com> <B36C6E37C9590A23002752A685256A01.0005189685256A01@webforums> <3A9D37FF.4040E319@sybase.com> <65A31CFA1B40F78600293F5A85256A02.0062F11B85256A01@webforums> <3AA5516E.50640CF5@sybase.com> <C8B4C45A177E081600747A6D85256A08.007696E385256A07@webforums>
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1138
Article PK: 854783

Thanks for the response.

But if I execute the procedure the way you have mentioned,
the procedure accesses the table from sybsystemprocs and not from the
database from which it is executed :

declare @exec_str varchar(255),@db_name varchar(30)
select @db_name="YOUR_DB"
select @exec_str=@db_name+"..sp_test_proc @var"
exec @exec_str

That is, Table2 in the cursor is from sybsystemprocs and not from @db_name
which is sent as a parameter.

Any other ideas ?

Ramesh