Sybase 101 question... 
Author Message
 Sybase 101 question...

Just can't figure this one out...

Suppose I have a table TAB like this:

VAR1  VAR2
----  ----
 a1    b1
 a2    b2
 a3    b3

Now I have a function f(VAR1, VAR2) that takes its two parameters
and returns a value - f is just a symbol, actual functionality does
not matter.

I have written a stored procedure like this:


as

And when I run it like this:



I get what I want.

Now is there any way to apply this stored procedure to all rows in my
table within a SELECT statement?  Something like this maybe:



from TAB

I tried this in several different way but to no avail.  Do I have to
use a cursor applying this sp to one row at a time?  Any suggestions?

Timur



Fri, 12 Dec 1997 03:00:00 GMT
 Sybase 101 question...


Quote:

>Just can't figure this one out...

>Suppose I have a table TAB like this:

>VAR1  VAR2
>----  ----
> a1    b1
> a2    b2
> a3    b3

>Now I have a function f(VAR1, VAR2) that takes its two parameters
>and returns a value - f is just a symbol, actual functionality does
>not matter.

>I have written a stored procedure like this:


>as

>And when I run it like this:



>I get what I want.

>Now is there any way to apply this stored procedure to all rows in my
>table within a SELECT statement?  Something like this maybe:



>from TAB

>I tried this in several different way but to no avail.  Do I have to
>use a cursor applying this sp to one row at a time?  Any suggestions?

>Timur


Yep,
You have to use a cursor.
--John Lewis


Mon, 15 Dec 1997 03:00:00 GMT
 Sybase 101 question...
There is  "CHEEZY" way to implement "cursors" yourself
(ie, if using sybase 4.X, or any version without cusrsors) by:

- Declaring a variable for each col in your table
- creating a new table (possibly temp), with the same cols
as your original table, and an additional "int" col, to be used
for control of iterations.
- get the values into it.
- set up a loop, using while and  set rowcounts, to pull
a row at a time off of the new table.  Execute the sp with
this set of values, and proceed to get the next row until
you have gotten all of the values.

e.g.,

create table #TAB
(
itr int,  /* this is an "iterator" */
VAR1  whatever,  /* whatever type U want */
VAR2  whatever
)

insert into #TAB
select 1, * from TAB




set rowcount 0

while ((select count(*) from #TAB where itr >0) > 0)
begin

set rowcount 1
select


from #TAB
where (itr > 0)   /* get only one row from set */

set rowcount 0    /* allow all rows for new actions */

/* Now we are done with these values: set itr to 0 */
set rowcount 1
update #TAB
set itr = 0


set rowcount 0
end

Very ugly, but it works.

- Dave



Sun, 21 Dec 1997 03:00:00 GMT
 Sybase 101 question...
There is  "CHEEZY" way to implement "cursors" yourself
(ie, if using sybase 4.X, or any version without cusrsors) by:

- Declaring a variable for each col in your table
- creating a new table (possibly temp), with the same cols
as your original table, and an additional "int" col, to be used
for control of iterations.
- get the values into it.
- set up a loop, using while and  set rowcounts, to pull
a row at a time off of the new table.  Execute the sp with
this set of values, and proceed to get the next row until
you have gotten all of the values.

e.g.,

create table #TAB
(
itr int,  /* this is an "iterator" */
VAR1  whatever,  /* whatever type U want */
VAR2  whatever
)

insert into #TAB
select 1, * from TAB




set rowcount 0

while ((select count(*) from #TAB where itr >0) > 0)
begin

set rowcount 1
select


from #TAB
where (itr > 0)   /* get only one row from set */

set rowcount 0    /* allow all rows for new actions */

/* Now we are done with these values: set itr to 0 */
set rowcount 1
update #TAB
set itr = 0


set rowcount 0
end

Very ugly, but it works.

- Dave



Sun, 21 Dec 1997 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. SQL 101 Question: How do you use the update command to update a date field

2. Question Number 100 and 101 - Many SELECTs in one SP....PLEASE HELP

3. A couple of DB 101 questions.

4. Snapshot Replication 101 Question

5. SQL 101 Question

6. CONVERT(datetime,GETDATE(),101) !!!!!!

7. Active/Active clusters 101

8. SANs 101

9. Please check this SQL code - SQL 101

10. CONVERT(VARCHAR(10), MyDate,101) AS MyDate

11. Triggers 101

12. 101, 103 for US and UK date formats


 
Powered by phpBB® Forum Software