[Sbo]

Hi,
I got a procedure that has 20 input variables i need to insert each variable into a table as a new row. Instead of writing 20 insert statements is there a way to loop them up or like auto increment variables in one insert statement.

Sybase Ver 11.9.2
----
example proc:
create proc1 as (@x1,@x2,@x3,@x4.....@x20)
as
insert into #tmp1 values(@x1)
insert into #tmp1 values(@x2)
.
.
.
insert into #tmp1 values(@x20)
-----
Thank you.

[willy_and_the_ci]

You can use Identity column

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server, that uniquely identifies the row within the table.


You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:

create table table_name (column_name numeric(precision,0) identity)

INSERT INTO table_name VALUES(@@identity)

Cheers

Willy

[Sbo]

Hi willy,
Thanks for the response. But the values that i get for these variables into the proc are not numbers they are charcter data of varchar(70).
I guess i forgot to mention this in my question sorry.
Any ideas willy.
example proc:
create proc1 as (@x1 varchar(70),@x2 varchar(70),@x3 varchar(70),@x4 varchar(70).....@x20 varchar(70))
as
insert into #tmp1 values(@x1)
insert into #tmp1 values(@x2)
.
. .
insert into #tmp1 values(@x20)
Thank you.

[MattR]

What exactly are you passing in to the stored procedure? If you could give an example of how this is used it would help. If it truely is 1, 2, 3, 4... 20 then I have no clue why you're passing them in ? I don't think 11.9.2 supports dynamic SQL so you can't perform a loop and insert them with EXEC( .. ). Unfortunately, I think you are going to have to write 20 insert statements. Maybe if you passed them as a single variable (maybe comma delimited?) you could break up the var based upon the comma and insert that in a while loop of some sort?

Or another solution, perform the insert from the client side (e.g. have the stored procedure take one parameter and have the client call it 20 times).

[Sbo]

Hi Matt,
The attachment as the details of the storproc. I think it will help to see what i am trying acomplish..

Thank you.