September 23, 2002 at 4:32 pm
How can I call a stored procedure multiple times,
i.e., for each record in a table by passing it
the record id? I am trying to avoid using a cursor
and looping through one record at a time.
I want to execute the Stored proc 100 times if there are
100 records in the table, at the same time,
programatically. THe SP will, for instance, create a table with the rec id as part of the table name and populate the table. I do not want to do it searially.
Please help
September 23, 2002 at 5:07 pm
Not sure exactly what you want.
I think this might work, although you might run into a character limitation size limit.
Of course this example only creates multiple tables based on data in a table with a few simple commands, instead of calling a SP multiple times.
Give it a shot.
This dynamically creates two tables(x_a, x_b) based on the records in table "ID".
create table id (id char(1))
insert into id values('a')
insert into id values('b')
declare @cmd char(8000)
set @cmd = ''
select @cmd = rtrim(@cmd) + 'create table x_' + id + '(field1 char(1))' + char(13) from
id
group by id
print @cmd
exec (@cmd)
drop table x_a, x_b, id
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 5:14 pm
Oh, now that I read what you want more closely, I'm not aware of a way to execute a proc and then start another one right way before the first call returns. I think all SQL scripts are sequential.
Although an alternative, would be to have a large number of SQL Server Agent jobs, like "JOB1", "JOB2", "JOB3", etc. and dynamically alter the jobs appropriately to suit your needs, and then start each job. Basically you would then have multiple jobs running at the same time.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 5:19 pm
Thanks, Greg. I am thinking the same myself,
that I need to execute each call to the SP as
a separate job. However, I think someone may have a more efficient way.
Thanks.
September 23, 2002 at 6:47 pm
Is this what you want, at least no cursor?
Create Table SomeMaster(RecId Int,a TinyInt,b TinyInt,c TinyInt)
Go
-- Populate
Insert SomeMaster values(1,11,12,13)
Insert SomeMaster values(2,21,22,23)
Insert SomeMaster values(3,31,32,33)
Insert SomeMaster values(4,41,42,43)
Go
Declare @intCurrent Int,
@TableName varchar(100)
Select @intCurrent=Min(RecId)
From SomeMaster
While Not (@intCurrent is Null)
Begin
Set @TableName='MyTable' +
Replicate('0',6-DataLength(Cast(@IntCurrent as varchar(10))))+
Cast(@IntCurrent as varchar(10))
Select @TableName
-- Check if exists already!
Create Table Tmp(RecId Int,a TinyInt,b TinyInt,c TinyInt)
Insert Tmp
Select *
From SomeMaster
Where RecId=@intCurrent
-- Give the real name
Exec Sp_Rename @objname = 'Tmp',@newname =@TableName,@objtype ='Object'
-- Get the next one
Select @intCurrent=Min(RecId)
From SomeMaster
Where RecId>@intCurrent
End
Go
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
----------------------------------------------------------------------------------------------------
MyTable000001
(1 row(s) affected)
(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.
The Object was renamed to 'MyTable000001'.
----------------------------------------------------------------------------------------------------
MyTable000002
(1 row(s) affected)
(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.
The Object was renamed to 'MyTable000002'.
----------------------------------------------------------------------------------------------------
MyTable000003
(1 row(s) affected)
(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.
The Object was renamed to 'MyTable000003'.
----------------------------------------------------------------------------------------------------
MyTable000004
(1 row(s) affected)
(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.
The Object was renamed to 'MyTable000004'.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply