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.

Stored proc a list and an in

7 posts in General Discussion Last posting was on 2004-09-24 19:05:17.0Z
Eric Posted on 2004-09-23 17:58:16.0Z
Sender: 2fdb.41530d26.1804289383@sybase.com
From: eric
Newsgroups: ianywhere.public.general
Subject: Stored proc a list and an in
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41530eb8.2ffc.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Sep 2004 10:58:16 -0700
X-Trace: forums-1-dub 1095962296 10.22.241.41 (23 Sep 2004 10:58:16 -0700)
X-Original-Trace: 23 Sep 2004 10:58:16 -0700, 10.22.241.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3609
Article PK: 7510

Hi -

Our application presents a list of items to the user where
they can change the status of 1 or more items to a given
value. I'd like to write a proc along the lines of

create proc @t1 varchar(1000), @t2 varchar(10)
as
begin

update customer
set AStatus = @t2
where ID in ( @t1 )

end

the parm would be a list of the IDs of the items selected,
comma separated like: 12,34,25,18,11

I get an error when I try this on ASA 9.0.1.1912: can not
convert ( 12,34,25,18,11 ) to numeric(30,6)

Is is possible to do what I'm thinking and if so, how?

Thanks
Eric


Nick Elson Posted on 2004-09-23 20:03:51.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <41530eb8.2ffc.1681692777@sybase.com>
Subject: Re: Stored proc a list and an in
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <41532c27@forums-1-dub>
Date: 23 Sep 2004 13:03:51 -0700
X-Trace: forums-1-dub 1095969831 10.25.100.143 (23 Sep 2004 13:03:51 -0700)
X-Original-Trace: 23 Sep 2004 13:03:51 -0700, nicelson-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3610
Article PK: 7511

Try execute immediate.

For example this works just fine with asademo.db


create variable str_x varchar(255);
set str_x = '101,102,103,105,105';
execute immediate with result set on 'select * from employee where
emp_id in ('+str_x+')';

<eric> wrote in message news:41530eb8.2ffc.1681692777@sybase.com...
> Hi -
>
> Our application presents a list of items to the user where
> they can change the status of 1 or more items to a given
> value. I'd like to write a proc along the lines of
>
> create proc @t1 varchar(1000), @t2 varchar(10)
> as
> begin
>
> update customer
> set AStatus = @t2
> where ID in ( @t1 )
>
> end
>
> the parm would be a list of the IDs of the items selected,
> comma separated like: 12,34,25,18,11
>
> I get an error when I try this on ASA 9.0.1.1912: can not
> convert ( 12,34,25,18,11 ) to numeric(30,6)
>
> Is is possible to do what I'm thinking and if so, how?
>
> Thanks
> Eric


Eric Posted on 2004-09-23 20:56:01.0Z
Sender: 32db.4153377d.1804289383@sybase.com
From: eric
Newsgroups: ianywhere.public.general
Subject: Re: Stored proc a list and an in
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41533861.32e4.1681692777@sybase.com>
References: <41532c27@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Sep 2004 13:56:01 -0700
X-Trace: forums-1-dub 1095972961 10.22.241.41 (23 Sep 2004 13:56:01 -0700)
X-Original-Trace: 23 Sep 2004 13:56:01 -0700, 10.22.241.41
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3612
Article PK: 7513

Thanks for the quick response. When I tried to alter the
proc per your example, I get a compile error saying 'syntax
error near 'result' SQLCODE -131 SQLSTATE 42000

If I remove the result set on .... and just have it as:

execute immediate

The proc compiles but won't run: Procedure 'immediate' not
found
SQLCODE 0265 State = '42S02'

Here's my proc:

ALTER PROCEDURE "DBA"."t1"(@v1 varchar(1000))
as
begin

declare @v2 varchar(1100)

set @v2 = 'select * from tbl_Customer where ID in ( ' +
@v1 + ' )'
message 'here is @v2: ', @v2 to client

execute immediate @v2
end

when I print @v2 it looks great. I just can't get it to
execute.

THanks
Eric

> Try execute immediate.
>
> For example this works just fine with asademo.db
>
>
> create variable str_x varchar(255);
> set str_x = '101,102,103,105,105';
> execute immediate with result set on 'select * from
> employee where emp_id in ('+str_x+')';
>
>
>
> <eric> wrote in message
> > news:41530eb8.2ffc.1681692777@sybase.com... Hi -
> >
> > Our application presents a list of items to the user
> > where they can change the status of 1 or more items to a
> > given value. I'd like to write a proc along the lines of
> >
> > create proc @t1 varchar(1000), @t2 varchar(10)
> > as
> > begin
> >
> > update customer
> > set AStatus = @t2
> > where ID in ( @t1 )
> >
> > end
> >
> > the parm would be a list of the IDs of the items
> > selected, comma separated like: 12,34,25,18,11
> >
> > I get an error when I try this on ASA 9.0.1.1912: can
> > not convert ( 12,34,25,18,11 ) to numeric(30,6)
> >
> > Is is possible to do what I'm thinking and if so, how?
> >
> > Thanks
> > Eric
>
>


Breck Carter [TeamSybase] Posted on 2004-09-24 08:37:14.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Stored proc a list and an in
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <v4n7l09fbijromja43da94q6vo5l1062l7@4ax.com>
References: <41532c27@forums-1-dub> <41533861.32e4.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 66.184.230.98
X-Original-Trace: 24 Sep 2004 01:37:10 -0700, 66.184.230.98
Lines: 85
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Sep 2004 01:37:12 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Sep 2004 01:37:14 -0700
X-Trace: forums-1-dub 1096015034 10.22.108.75 (24 Sep 2004 01:37:14 -0700)
X-Original-Trace: 24 Sep 2004 01:37:14 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3613
Article PK: 7515

Try EXECUTE ( @v2 )

On 23 Sep 2004 13:56:01 -0700, eric wrote:

>Thanks for the quick response. When I tried to alter the
>proc per your example, I get a compile error saying 'syntax
>error near 'result' SQLCODE -131 SQLSTATE 42000
>
> If I remove the result set on .... and just have it as:
>
> execute immediate
>
> The proc compiles but won't run: Procedure 'immediate' not
>found
>SQLCODE 0265 State = '42S02'
>
> Here's my proc:
>
>ALTER PROCEDURE "DBA"."t1"(@v1 varchar(1000))
>as
>begin
>
> declare @v2 varchar(1100)
>
> set @v2 = 'select * from tbl_Customer where ID in ( ' +
>@v1 + ' )'
> message 'here is @v2: ', @v2 to client
>
> execute immediate @v2
>end
>
> when I print @v2 it looks great. I just can't get it to
>execute.
>
> THanks
> Eric
>
>> Try execute immediate.
>>
>> For example this works just fine with asademo.db
>>
>>
>> create variable str_x varchar(255);
>> set str_x = '101,102,103,105,105';
>> execute immediate with result set on 'select * from
>> employee where emp_id in ('+str_x+')';
>>
>>
>>
>> <eric> wrote in message
>> > news:41530eb8.2ffc.1681692777@sybase.com... Hi -
>> >
>> > Our application presents a list of items to the user
>> > where they can change the status of 1 or more items to a
>> > given value. I'd like to write a proc along the lines of
>> >
>> > create proc @t1 varchar(1000), @t2 varchar(10)
>> > as
>> > begin
>> >
>> > update customer
>> > set AStatus = @t2
>> > where ID in ( @t1 )
>> >
>> > end
>> >
>> > the parm would be a list of the IDs of the items
>> > selected, comma separated like: 12,34,25,18,11
>> >
>> > I get an error when I try this on ASA 9.0.1.1912: can
>> > not convert ( 12,34,25,18,11 ) to numeric(30,6)
>> >
>> > Is is possible to do what I'm thinking and if so, how?
>> >
>> > Thanks
>> > Eric
>>
>>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Eric Posted on 2004-09-24 17:51:27.0Z
Sender: 32db.4153377d.1804289383@sybase.com
From: eric
Newsgroups: ianywhere.public.general
Subject: Re: Stored proc a list and an in
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41545e9f.4285.1681692777@sybase.com>
References: <v4n7l09fbijromja43da94q6vo5l1062l7@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Sep 2004 10:51:27 -0700
X-Trace: forums-1-dub 1096048287 10.22.241.41 (24 Sep 2004 10:51:27 -0700)
X-Original-Trace: 24 Sep 2004 10:51:27 -0700, 10.22.241.41
Lines: 93
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3616
Article PK: 7517

Ok, I tried it this way and get the following error:
Result set not permitted in '<batch statement>'
SQLCODE -946 State 07005

Eric

> Try EXECUTE ( @v2 )
>
> On 23 Sep 2004 13:56:01 -0700, eric wrote:
>
> >Thanks for the quick response. When I tried to alter the
> >proc per your example, I get a compile error saying
> 'syntax >error near 'result' SQLCODE -131 SQLSTATE 42000
> >
> > If I remove the result set on .... and just have it as:
> >
> > execute immediate
> >
> > The proc compiles but won't run: Procedure 'immediate'
> not >found
> >SQLCODE 0265 State = '42S02'
> >
> > Here's my proc:
> >
> >ALTER PROCEDURE "DBA"."t1"(@v1 varchar(1000))
> >as
> >begin
> >
> > declare @v2 varchar(1100)
> >
> > set @v2 = 'select * from tbl_Customer where ID in ( ' +
> >@v1 + ' )'
> > message 'here is @v2: ', @v2 to client
> >
> > execute immediate @v2
> >end
> >
> > when I print @v2 it looks great. I just can't get it to
> >execute.
> >
> > THanks
> > Eric
> >
> >> Try execute immediate.
> >>
> >> For example this works just fine with asademo.db
> >>
> >>
> >> create variable str_x varchar(255);
> >> set str_x = '101,102,103,105,105';
> >> execute immediate with result set on 'select * from
> >> employee where emp_id in ('+str_x+')';
> >>
> >>
> >>
> >> <eric> wrote in message
> >> > news:41530eb8.2ffc.1681692777@sybase.com... Hi -
> >> >
> >> > Our application presents a list of items to the user
> >> > where they can change the status of 1 or more items
> to a >> > given value. I'd like to write a proc along the
> lines of >> >
> >> > create proc @t1 varchar(1000), @t2 varchar(10)
> >> > as
> >> > begin
> >> >
> >> > update customer
> >> > set AStatus = @t2
> >> > where ID in ( @t1 )
> >> >
> >> > end
> >> >
> >> > the parm would be a list of the IDs of the items
> >> > selected, comma separated like: 12,34,25,18,11
> >> >
> >> > I get an error when I try this on ASA 9.0.1.1912:
> can >> > not convert ( 12,34,25,18,11 ) to numeric(30,6)
> >> >
> >> > Is is possible to do what I'm thinking and if so,
> how? >> >
> >> > Thanks
> >> > Eric
> >>
> >>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Nick Elson Posted on 2004-09-24 18:35:58.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <v4n7l09fbijromja43da94q6vo5l1062l7@4ax.com> <41545e9f.4285.1681692777@sybase.com>
Subject: Re: Stored proc a list and an in
Lines: 102
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <4154690e$1@forums-1-dub>
Date: 24 Sep 2004 11:35:58 -0700
X-Trace: forums-1-dub 1096050958 10.25.100.143 (24 Sep 2004 11:35:58 -0700)
X-Original-Trace: 24 Sep 2004 11:35:58 -0700, nicelson-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3617
Article PK: 7518

So ... go back to my original suggestion ...
I expected you had a result set and intentionally added that clause for that
reason.

So why did you feel the need to remove it?

<eric> wrote in message news:41545e9f.4285.1681692777@sybase.com...
> Ok, I tried it this way and get the following error:
> Result set not permitted in '<batch statement>'
> SQLCODE -946 State 07005
>
> Eric
>
> > Try EXECUTE ( @v2 )
> >
> > On 23 Sep 2004 13:56:01 -0700, eric wrote:
> >
> > >Thanks for the quick response. When I tried to alter the
> > >proc per your example, I get a compile error saying
> > 'syntax >error near 'result' SQLCODE -131 SQLSTATE 42000
> > >
> > > If I remove the result set on .... and just have it as:
> > >
> > > execute immediate
> > >
> > > The proc compiles but won't run: Procedure 'immediate'
> > not >found
> > >SQLCODE 0265 State = '42S02'
> > >
> > > Here's my proc:
> > >
> > >ALTER PROCEDURE "DBA"."t1"(@v1 varchar(1000))
> > >as
> > >begin
> > >
> > > declare @v2 varchar(1100)
> > >
> > > set @v2 = 'select * from tbl_Customer where ID in ( ' +
> > >@v1 + ' )'
> > > message 'here is @v2: ', @v2 to client
> > >
> > > execute immediate @v2
> > >end
> > >
> > > when I print @v2 it looks great. I just can't get it to
> > >execute.
> > >
> > > THanks
> > > Eric
> > >
> > >> Try execute immediate.
> > >>
> > >> For example this works just fine with asademo.db
> > >>
> > >>
> > >> create variable str_x varchar(255);
> > >> set str_x = '101,102,103,105,105';
> > >> execute immediate with result set on 'select * from
> > >> employee where emp_id in ('+str_x+')';
> > >>
> > >>
> > >>
> > >> <eric> wrote in message
> > >> > news:41530eb8.2ffc.1681692777@sybase.com... Hi -
> > >> >
> > >> > Our application presents a list of items to the user
> > >> > where they can change the status of 1 or more items
> > to a >> > given value. I'd like to write a proc along the
> > lines of >> >
> > >> > create proc @t1 varchar(1000), @t2 varchar(10)
> > >> > as
> > >> > begin
> > >> >
> > >> > update customer
> > >> > set AStatus = @t2
> > >> > where ID in ( @t1 )
> > >> >
> > >> > end
> > >> >
> > >> > the parm would be a list of the IDs of the items
> > >> > selected, comma separated like: 12,34,25,18,11
> > >> >
> > >> > I get an error when I try this on ASA 9.0.1.1912:
> > can >> > not convert ( 12,34,25,18,11 ) to numeric(30,6)
> > >> >
> > >> > Is is possible to do what I'm thinking and if so,
> > how? >> >
> > >> > Thanks
> > >> > Eric
> > >>
> > >>
> >
> > --
> > SQL Anywhere Studio 9 Developer's Guide
> > Buy the book:
> >
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> > bcarter@risingroad.com
> > RisingRoad SQL Anywhere and MobiLink Professional Services
> > www.risingroad.com


"Bruce Hay" <h_a_y Posted on 2004-09-24 19:05:17.0Z
From: "Bruce Hay" <h_a_y@i~a~n~y~w~h~e~r~e.com>
Newsgroups: ianywhere.public.general
References: <v4n7l09fbijromja43da94q6vo5l1062l7@4ax.com> <41545e9f.4285.1681692777@sybase.com> <4154690e$1@forums-1-dub>
Subject: Re: Stored proc a list and an in
Lines: 121
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: hay-t30.sybase.com
X-Original-NNTP-Posting-Host: hay-t30.sybase.com
Message-ID: <41546fed$1@forums-1-dub>
Date: 24 Sep 2004 12:05:17 -0700
X-Trace: forums-1-dub 1096052717 10.25.99.98 (24 Sep 2004 12:05:17 -0700)
X-Original-Trace: 24 Sep 2004 12:05:17 -0700, hay-t30.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3618
Article PK: 7516

Try:

alter procedure dba.t1( @v1 long varchar )
begin
execute immediate with result set on
'select * from tbl_Customer where ID in (' || @v1 || ')';
end

Your original statement was an UPDATE, not a SELECT. For that case, you can
omit the WITH RESULT SET clause.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:4154690e$1@forums-1-dub...
> So ... go back to my original suggestion ...
> I expected you had a result set and intentionally added that clause for
that
> reason.
>
> So why did you feel the need to remove it?
>
> <eric> wrote in message news:41545e9f.4285.1681692777@sybase.com...
> > Ok, I tried it this way and get the following error:
> > Result set not permitted in '<batch statement>'
> > SQLCODE -946 State 07005
> >
> > Eric
> >
> > > Try EXECUTE ( @v2 )
> > >
> > > On 23 Sep 2004 13:56:01 -0700, eric wrote:
> > >
> > > >Thanks for the quick response. When I tried to alter the
> > > >proc per your example, I get a compile error saying
> > > 'syntax >error near 'result' SQLCODE -131 SQLSTATE 42000
> > > >
> > > > If I remove the result set on .... and just have it as:
> > > >
> > > > execute immediate
> > > >
> > > > The proc compiles but won't run: Procedure 'immediate'
> > > not >found
> > > >SQLCODE 0265 State = '42S02'
> > > >
> > > > Here's my proc:
> > > >
> > > >ALTER PROCEDURE "DBA"."t1"(@v1 varchar(1000))
> > > >as
> > > >begin
> > > >
> > > > declare @v2 varchar(1100)
> > > >
> > > > set @v2 = 'select * from tbl_Customer where ID in ( ' +
> > > >@v1 + ' )'
> > > > message 'here is @v2: ', @v2 to client
> > > >
> > > > execute immediate @v2
> > > >end
> > > >
> > > > when I print @v2 it looks great. I just can't get it to
> > > >execute.
> > > >
> > > > THanks
> > > > Eric
> > > >
> > > >> Try execute immediate.
> > > >>
> > > >> For example this works just fine with asademo.db
> > > >>
> > > >>
> > > >> create variable str_x varchar(255);
> > > >> set str_x = '101,102,103,105,105';
> > > >> execute immediate with result set on 'select * from
> > > >> employee where emp_id in ('+str_x+')';
> > > >>
> > > >>
> > > >>
> > > >> <eric> wrote in message
> > > >> > news:41530eb8.2ffc.1681692777@sybase.com... Hi -
> > > >> >
> > > >> > Our application presents a list of items to the user
> > > >> > where they can change the status of 1 or more items
> > > to a >> > given value. I'd like to write a proc along the
> > > lines of >> >
> > > >> > create proc @t1 varchar(1000), @t2 varchar(10)
> > > >> > as
> > > >> > begin
> > > >> >
> > > >> > update customer
> > > >> > set AStatus = @t2
> > > >> > where ID in ( @t1 )
> > > >> >
> > > >> > end
> > > >> >
> > > >> > the parm would be a list of the IDs of the items
> > > >> > selected, comma separated like: 12,34,25,18,11
> > > >> >
> > > >> > I get an error when I try this on ASA 9.0.1.1912:
> > > can >> > not convert ( 12,34,25,18,11 ) to numeric(30,6)
> > > >> >
> > > >> > Is is possible to do what I'm thinking and if so,
> > > how? >> >
> > > >> > Thanks
> > > >> > Eric
> > > >>
> > > >>
> > >
> > > --
> > > SQL Anywhere Studio 9 Developer's Guide
> > > Buy the book:
> > >
> > http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> > > bcarter@risingroad.com
> > > RisingRoad SQL Anywhere and MobiLink Professional Services
> > > www.risingroad.com
>
>