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.

store procs SQL

8 posts in General Discussion Last posting was on 2005-05-02 17:03:41.0Z
stevetx30 Posted on 2005-04-29 22:30:30.0Z
Sender: 62f5.4272b53e.1804289383@sybase.com
From: stevetx30
Newsgroups: ianywhere.public.general
Subject: store procs SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4272b580.62f7.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 29 Apr 2005 15:30:24 -0700, 10.22.241.42
Lines: 19
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Apr 2005 15:30:27 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Apr 2005 15:30:30 -0700
X-Trace: forums-1-dub 1114813830 10.22.108.75 (29 Apr 2005 15:30:30 -0700)
X-Original-Trace: 29 Apr 2005 15:30:30 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4424
Article PK: 8234

ASA 9.0.1
Windows 2000

I am new to building stored procs.
I need to create a proc which could have more than one row
as a result set.
My IN’s are STATE and ZIP which will be in my where
clause.
What I want to do is build that SQL statement based on my
inputs.

So I would have my select statement something like
SELECT employee_id from employee Where ?
I do not want to have a WHERE clause if state and zip is
null

What are the steps I need to do to accomplish this.

Thanks


Breck Carter [TeamSybase] Posted on 2005-04-30 16:08:17.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: store procs SQL
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <66b771t1itl8csrag31gdbup4eq0j6shkh@4ax.com>
References: <4272b580.62f7.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 208.254.244.99
X-Original-NNTP-Posting-Host: 208.254.244.99
Date: 30 Apr 2005 09:08:17 -0700
X-Trace: forums-1-dub 1114877297 208.254.244.99 (30 Apr 2005 09:08:17 -0700)
X-Original-Trace: 30 Apr 2005 09:08:17 -0700, 208.254.244.99
Lines: 33
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4426
Article PK: 8235

SELECT employee_id FROM employee
WHERE ( employee.state = p_state OR p_state IS NULL )
AND ( employee.zip = p_zip OR p_zip IS NULL );

On 29 Apr 2005 15:30:30 -0700, stevetx30 wrote:

>ASA 9.0.1
>Windows 2000
>
>I am new to building stored procs.
> I need to create a proc which could have more than one row
>as a result set.
> My IN’s are STATE and ZIP which will be in my where
>clause.
>What I want to do is build that SQL statement based on my
>inputs.
>
>So I would have my select statement something like
> SELECT employee_id from employee Where ?
>I do not want to have a WHERE clause if state and zip is
>null
>
>What are the steps I need to do to accomplish this.
>
>Thanks

--
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


Greg Fenton Posted on 2005-04-30 23:42:58.0Z
Message-ID: <427417FC.5050609@ianywhere.com>
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
To: NOSPAM__bcarter@risingroad.com
Subject: Re: store procs SQL
References: <4272b580.62f7.1681692777@sybase.com> <66b771t1itl8csrag31gdbup4eq0j6shkh@4ax.com>
In-Reply-To: <66b771t1itl8csrag31gdbup4eq0j6shkh@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-Trace: 30 Apr 2005 16:42:54 -0700, cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 18
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 30 Apr 2005 16:42:55 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 30 Apr 2005 16:42:58 -0700
X-Trace: forums-1-dub 1114904578 10.22.108.75 (30 Apr 2005 16:42:58 -0700)
X-Original-Trace: 30 Apr 2005 16:42:58 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4427
Article PK: 8233


Breck Carter [TeamSybase] wrote:
> SELECT employee_id FROM employee
> WHERE ( employee.state = p_state OR p_state IS NULL )
> AND ( employee.zip = p_zip OR p_zip IS NULL );
>

Yep, that's answering the question he asked...I had somehow thrown in
the idea that there were going to be a variable number of states and/or
zips....I gotta get more sleep on the weekends :-)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Breck Carter [TeamSybase] Posted on 2005-05-01 14:07:31.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: store procs SQL
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <iio971d5k79842cmusi1lj5njq7seharjn@4ax.com>
References: <4272b580.62f7.1681692777@sybase.com> <66b771t1itl8csrag31gdbup4eq0j6shkh@4ax.com> <427417FC.5050609@ianywhere.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 208.254.244.99
X-Original-NNTP-Posting-Host: 208.254.244.99
Date: 1 May 2005 07:07:31 -0700
X-Trace: forums-1-dub 1114956451 208.254.244.99 (1 May 2005 07:07:31 -0700)
X-Original-Trace: 1 May 2005 07:07:31 -0700, 208.254.244.99
Lines: 27
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4428
Article PK: 8238

No, both answers are valid. You're getting enough sleep on the
weekends... if you need more sleep, you should get it *at work* like
everyone else :)

Breck

On 30 Apr 2005 16:42:58 -0700, Greg Fenton

<greg.fenton_NOSPAM_@ianywhere.com> wrote:

>Breck Carter [TeamSybase] wrote:
>> SELECT employee_id FROM employee
>> WHERE ( employee.state = p_state OR p_state IS NULL )
>> AND ( employee.zip = p_zip OR p_zip IS NULL );
>>
>
>Yep, that's answering the question he asked...I had somehow thrown in
>the idea that there were going to be a variable number of states and/or
>zips....I gotta get more sleep on the weekends :-)
>
>greg.fenton

--
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


stevetx30 Posted on 2005-05-02 14:40:26.0Z
Sender: 4924.42762f3f.1804289383@sybase.com
From: stevetx30
Newsgroups: ianywhere.public.general
Subject: Re: store procs SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42763bda.4990.1681692777@sybase.com>
References: <iio971d5k79842cmusi1lj5njq7seharjn@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 May 2005 07:40:26 -0700
X-Trace: forums-1-dub 1115044826 10.22.241.41 (2 May 2005 07:40:26 -0700)
X-Original-Trace: 2 May 2005 07:40:26 -0700, 10.22.241.41
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4429
Article PK: 8239

Thanks for your help
I have what Breck sugested in the Datawindow working fine
howerver I really want to try this in a stored proc and
build the statement based on what I need.
I did try this and got an error -946 which is (You attempted
to execute a SELECT statement in a context where a result
set is not permitted.)

begin
DECLARE String1 LONG VARCHAR;
SET String1 = 'SELECT emp_id from employee';
EXECUTE IMMEDIATE String1;
end

>
No, both answers are valid. You're getting enough sleep on
> the weekends... if you need more sleep, you should get it
> *at work* like everyone else :)
>
> Breck
>
> On 30 Apr 2005 16:42:58 -0700, Greg Fenton
> <greg.fenton_NOSPAM_@ianywhere.com> wrote:
>
> >Breck Carter [TeamSybase] wrote:
> >> SELECT employee_id FROM employee
> >> WHERE ( employee.state = p_state OR p_state IS NULL )
> >> AND ( employee.zip = p_zip OR p_zip IS NULL );
> >>
> >
> >Yep, that's answering the question he asked...I had
> somehow thrown in >the idea that there were going to be a
> variable number of states and/or >zips....I gotta get
> more sleep on the weekends :-) >
> >greg.fenton
>
> --
> 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


Chris Keating (iAnywhere Solutions) Posted on 2005-05-02 16:29:05.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <Spam_NoThanks_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <iio971d5k79842cmusi1lj5njq7seharjn@4ax.com> <42763bda.4990.1681692777@sybase.com>
Subject: Re: store procs SQL
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
X-Original-NNTP-Posting-Host: keating-xp2.sybase.com
Message-ID: <4276554e@forums-2-dub>
X-Original-Trace: 2 May 2005 09:29:02 -0700, keating-xp2.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 2 May 2005 09:29:02 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 2 May 2005 09:29:05 -0700
X-Trace: forums-1-dub 1115051345 10.22.108.75 (2 May 2005 09:29:05 -0700)
X-Original-Trace: 2 May 2005 09:29:05 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4430
Article PK: 8240

Try

EXECUTE IMMEDIATE WITH RESULT SET ON String1;

<stevetx30> wrote in message news:42763bda.4990.1681692777@sybase.com...
> Thanks for your help
> I have what Breck sugested in the Datawindow working fine
> howerver I really want to try this in a stored proc and
> build the statement based on what I need.
> I did try this and got an error -946 which is (You attempted
> to execute a SELECT statement in a context where a result
> set is not permitted.)
>
> begin
> DECLARE String1 LONG VARCHAR;
> SET String1 = 'SELECT emp_id from employee';
> EXECUTE IMMEDIATE String1;
> end
>
>
>
>>
> No, both answers are valid. You're getting enough sleep on
>> the weekends... if you need more sleep, you should get it
>> *at work* like everyone else :)
>>
>> Breck
>>
>> On 30 Apr 2005 16:42:58 -0700, Greg Fenton
>> <greg.fenton_NOSPAM_@ianywhere.com> wrote:
>>
>> >Breck Carter [TeamSybase] wrote:
>> >> SELECT employee_id FROM employee
>> >> WHERE ( employee.state = p_state OR p_state IS NULL )
>> >> AND ( employee.zip = p_zip OR p_zip IS NULL );
>> >>
>> >
>> >Yep, that's answering the question he asked...I had
>> somehow thrown in >the idea that there were going to be a
>> variable number of states and/or >zips....I gotta get
>> more sleep on the weekends :-) >
>> >greg.fenton
>>
>> --
>> 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


stevetx30 Posted on 2005-05-02 17:03:41.0Z
Sender: 1677.42765d0c.1804289383@sybase.com
From: stevetx30
Newsgroups: ianywhere.public.general
Subject: Re: store procs SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42765d69.167d.1681692777@sybase.com>
References: <4276554e@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 2 May 2005 10:03:37 -0700, 10.22.241.42
Lines: 64
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 2 May 2005 10:03:38 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 2 May 2005 10:03:41 -0700
X-Trace: forums-1-dub 1115053421 10.22.108.75 (2 May 2005 10:03:41 -0700)
X-Original-Trace: 2 May 2005 10:03:41 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4431
Article PK: 8247

Thanks!
That worked

thanks Greg,Breck and Chris.

Steve

> Try
>
> EXECUTE IMMEDIATE WITH RESULT SET ON String1;
>
>
> <stevetx30> wrote in message
> > news:42763bda.4990.1681692777@sybase.com... Thanks for
> > your help I have what Breck sugested in the Datawindow
> > working fine howerver I really want to try this in a
> > stored proc and build the statement based on what I
> > need. I did try this and got an error -946 which is (You
> > attempted to execute a SELECT statement in a context
> > where a result set is not permitted.)
> >
> > begin
> > DECLARE String1 LONG VARCHAR;
> > SET String1 = 'SELECT emp_id from employee';
> > EXECUTE IMMEDIATE String1;
> > end
> >
> >
> >
> >>
> > No, both answers are valid. You're getting enough sleep
> on >> the weekends... if you need more sleep, you should
> get it >> *at work* like everyone else :)
> >>
> >> Breck
> >>
> >> On 30 Apr 2005 16:42:58 -0700, Greg Fenton
> >> <greg.fenton_NOSPAM_@ianywhere.com> wrote:
> >>
> >> >Breck Carter [TeamSybase] wrote:
> >> >> SELECT employee_id FROM employee
> >> >> WHERE ( employee.state = p_state OR p_state IS NULL
> ) >> >> AND ( employee.zip = p_zip OR p_zip IS NULL );
> >> >>
> >> >
> >> >Yep, that's answering the question he asked...I had
> >> somehow thrown in >the idea that there were going to
> be a >> variable number of states and/or >zips....I gotta
> get >> more sleep on the weekends :-) >
> >> >greg.fenton
> >>
> >> --
> >> 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
>
>


Greg Fenton Posted on 2005-04-30 03:46:23.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: store procs SQL
References: <4272b580.62f7.1681692777@sybase.com>
In-Reply-To: <4272b580.62f7.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <4272ff8b$1@forums-2-dub>
X-Original-Trace: 29 Apr 2005 20:46:19 -0700, cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 32
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Apr 2005 20:46:20 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Apr 2005 20:46:23 -0700
X-Trace: forums-1-dub 1114832783 10.22.108.75 (29 Apr 2005 20:46:23 -0700)
X-Original-Trace: 29 Apr 2005 20:46:23 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4425
Article PK: 8237


stevetx30 wrote:
> What I want to do is build that SQL statement based on my
> inputs.
>

In the SQLAnywhere 9.x online docs, see the EXECUTE IMMEDIATE statement:

ASA SQL Reference
SQL Statements
- EXECUTE IMMEDIATE statement [SP]

Also see:

ASA SQL User's Guide
Using Procedures, Triggers, and Batches
- Using the EXECUTE IMMEDIATE statement in procedures


So basically you build a string that contains your query and you use
EXECUTE IMMEDIATE to execute that query. As of 9.0.1, you can use
EXECUTE IMMEDIATE to return a result set from a stored procedure.


Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/