Executing procedure for all rows in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Executing procedure for all rows in a column

Author

Topic

axux23
Starting Member

4 Posts

Posted - 2009-02-11 : 22:59:41

Hi again,
I want to execute a stored procedure that requires one variable.

Example :
Exec myprocedure 'myvariable'

The question is ;
I have a column in my table with variables on its rows.
How can i execute this procedure for all variables in that column?

--My column--
variable1
variable2
variable3
variable4

I want it to be executed step by step like that :
Exec myprocedure 'variable1'
Exec myprocedure 'variable2' ....

I have more than 5000 rows in that column, so it will be too hard to execute it manually, how can i do that with a single code ?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 23:42:01

[code]

Try like this

create table variables(id int identity(1,1),variable varchar(555))
insert into variables
select 'paul' union all
select 'kiran'

create proc [dbo].[usp_lengthfinder]
(
@string varchar(555),
@len int output
)
as
set nocount on
begin
select @len = len(@string)
select @len
end
set nocount off

declare @id int
select @id = min(id) from variables
while(@id<= (select max(id) from variables))
begin
declare @var varchar(444)
select @var = variable from variables where id = @id
exec dbo.usp_lengthfinder @var,null
select @id = min(id) from variables where id > @id
end

[/code]

Jai Krishna

axux23
Starting Member

4 Posts

Posted - 2009-02-12 : 00:34:43

Is there a more simple way to do that ? I don't have an id number(int) column on my table.
Executing them in a row isn't that important, but all variables must be executed by that procedure.
Thanks for your answer.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-12 : 00:44:30

what columns do you have in your table other than 'My Column' ??

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-12 : 00:58:45

[code]
Try like this

select identity(int,1,1) as id,* into temptable from urtable

declare @id int
select @id = min(id) from temptable
while(@id<= (select max(id) from temptable ))
begin
declare @var varchar(444)
select @var = variable from temptable where id = @id
exec dbo.usp_lengthfinder @var,null
select @id = min(id) from temptable where id > @id
end

drop table temptable

Here variable means ur mycolumn field
[/code]

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 00:59:52

Just substitute @id variable and id column name with the column name and datatype you have in your environment.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:36:41

quote:
Originally posted by axux23

Hi again,
I want to execute a stored procedure that requires one variable.

Example :
Exec myprocedure 'myvariable'

The question is ;
I have a column in my table with variables on its rows.
How can i execute this procedure for all variables in that column?

--My column--
variable1
variable2
variable3
variable4

I want it to be executed step by step like that :
Exec myprocedure 'variable1'
Exec myprocedure 'variable2' ....

I have more than 5000 rows in that column, so it will be too hard to execute it manually, how can i do that with a single code ?

what does stored procedure do? is it returning a single value or resultset?

axux23
Starting Member

4 Posts

Posted - 2009-02-12 : 21:04:31

This procedure finds information for 'variable' from other tables (about 200 rows for each variable) and inserts that 200-rows of info to my "info" table.

If i execute two of them manually (example):

Exec myprocedure 'variable1'
Exec myprocedure 'variable2'
As a result, 400 rows of info will be inserted into my "info" table.

I have about 5000 variables and i want to execute this procedure on each variable. 5000 variable x 200 info-row and there will be about 1000000 rows of info in the "info" table.
That may be slow to execute the procedure about 5000 times, but i have to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 21:14:00

ok. why not pass an array of variables to procedure and return all related records by a single call rather than calling sp inside a loop?
something like

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources