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.

Migrating ASE SPs to ASA 10

2 posts in General Discussion Last posting was on 2007-03-19 14:29:28.0Z
Carlos Arienti Posted on 2007-03-16 17:57:45.0Z
From: "Carlos Arienti" <carienti@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Migrating ASE SPs to ASA 10
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 189.140.132.239
X-Original-NNTP-Posting-Host: 189.140.132.239
Message-ID: <45fada99$1@forums-1-dub>
Date: 16 Mar 2007 09:57:45 -0800
X-Trace: forums-1-dub 1174067865 189.140.132.239 (16 Mar 2007 09:57:45 -0800)
X-Original-Trace: 16 Mar 2007 09:57:45 -0800, 189.140.132.239
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5895
Article PK: 1877

Hi, I have the following ASE SP:
create procedure dinamico2
@beg_date datetime, @end_date datetime
as
begin
declare @cadena varchar(200)
select @cadena = 'select * from reporte where fecha_robo between "' +
convert(char(20),@beg_date) + '" and "' + convert(char(20),@end_date) + '"'
EXECUTE (@cadena)
end

I create it in the interactive SQL ASA 10, and get the ASA syntax as
follows:
ALTER PROCEDURE "DBA"."dinamico2"
@beg_date datetime, @end_date datetime
as
begin
declare @cadena varchar(200)
select @cadena = 'select * from reporte where fecha_robo between "' +
convert(char(20),@beg_date) + '" and "' + convert(char(20),@end_date) + '"'
EXECUTE (@cadena)
end

But when I execute it (EXECUTE dinamico2 "2007/01/01", "2007/01/31"), I get
a "Result set not permitted in batch" error.

What do I have to do to have it running as in ASE? The app will have to run
on both ASE and ASA so I want to keep one code.
Thanks


"Nick Elson" < Posted on 2007-03-19 14:29:28.0Z
From: "Nick Elson" <@n@o@.@s@p@a@m@.@n@i@c@k@.@e@l@s@o@n@s@y@b@a@s@e@.@c@o@m@>
Newsgroups: ianywhere.public.general
References: <45fada99$1@forums-1-dub>
Subject: Re: Migrating ASE SPs to ASA 10
Lines: 80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: nicelson-m20.sybase.com
X-Original-NNTP-Posting-Host: nicelson-m20.sybase.com
Message-ID: <45fe9e48$1@forums-1-dub>
Date: 19 Mar 2007 06:29:28 -0800
X-Trace: forums-1-dub 1174314568 10.25.98.215 (19 Mar 2007 06:29:28 -0800)
X-Original-Trace: 19 Mar 2007 06:29:28 -0800, nicelson-m20.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5900
Article PK: 1881

Actually, that's still Transact SQL but the manual offers a
way to operate still

SQL Anywhere® Server - SQL Reference
SQL Statements
EXECUTE statement [T-SQL]
. . .
Usage
. . .
"The Transact-SQL EXECUTE statement does not have a way to signify that
a result
set is expected. One way to indicate that a Transact-SQL procedure
returns a result
set is to include something like the following:
IF 1 = 0 THEN
SELECT 1 AS a
. . . "

as in

ALTER PROCEDURE "DBA"."dinamico2"
@beg_date datetime, @end_date datetime
as
begin
declare @cadena varchar(200)
IF 1 = 0 THEN SELECT 1 AS a
select @cadena = 'select * from reporte where fecha_robo between "'
+
convert(char(20),@beg_date) + '" and "' +
convert(char(20),@end_date) + '"'
EXECUTE (@cadena)
end


Alternative utilize the SQL Anywhere SQL statement
EXECUTE IMMEDIATE ... WITH RESULT SET ON;

"Carlos Arienti" <carienti@gmail.com> wrote in message
news:45fada99$1@forums-1-dub...
> Hi, I have the following ASE SP:
> create procedure dinamico2
> @beg_date datetime, @end_date datetime
> as
> begin
> declare @cadena varchar(200)
> select @cadena = 'select * from reporte where fecha_robo between "' +
> convert(char(20),@beg_date) + '" and "' + convert(char(20),@end_date) +
> '"'
> EXECUTE (@cadena)
> end
>
> I create it in the interactive SQL ASA 10, and get the ASA syntax as
> follows:
> ALTER PROCEDURE "DBA"."dinamico2"
> @beg_date datetime, @end_date datetime
> as
> begin
> declare @cadena varchar(200)
> select @cadena = 'select * from reporte where fecha_robo between "' +
> convert(char(20),@beg_date) + '" and "' + convert(char(20),@end_date) +
> '"'
> EXECUTE (@cadena)
> end
>
> But when I execute it (EXECUTE dinamico2 "2007/01/01", "2007/01/31"), I
> get a "Result set not permitted in batch" error.
>
> What do I have to do to have it running as in ASE? The app will have to
> run on both ASE and ASA so I want to keep one code.
> Thanks
>