Stored Procedure Loop - SQL Server Forums

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

Transact-SQL (2005)

Stored Procedure Loop

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-08-25 : 14:40:22

I need to create a loop in my sotred procedure to loop through a table till it gets all records and then goes to next procedure. Currently I have it so that selects a filter and then goes to the next table but it only grabs the first record and then exists and grabs the next filter and first personnel record and so on. I need it to grab the first filter go thorugh all of the personnel records asociated with that filter, save records and then move on to next filter. Here is my stored procedure I have markerd where I need a loop of some kind to process those records.

Alter Proc sp_demo2
AS
declare @counter int,
@countfilter int,
@FilterID int,
@AssignedID int,
@UnitID int,
@TaskName varchar(60),
@ApproveLev varchar(15),
@Sql nvarchar(4000)

Select @countfilter = count(uf.intFilterId) from tblUnitFilters as uf Inner join tblFilter as f on f.intFilterID = uf.intFilterId

set @counter = 1

while @counter <= @countfilter
begin

Select @FilterID = intFilterId,
@AssignedId = intAssignedId,
@UnitId = intUnitMobId,
@Taskname = strTaskname,
@ApproveLev = strApprovalLevel,
@Sql = strSql
from (Select uf.intFilterId,
uf.intAssignedID,
uf.intUnitMobId,
f.strTaskname,
f.strApprovalLevel,
f.strSql,
row_number() over (order by uf.intfilterID) rn from tblUnitFilters as uf Inner join
tblFilter as f on f.intFilterID = uf.intFilterId where uf.dtRemoved is NULL) o where rn = @counter

--- Call the SQL Filter and assign the unit # as the variable to get at table

Declare @PositionId int,
@Para varchar(4),
@Line varchar(3),
@Posn varchar(60),
@Sid varchar(60),
@Name varchar(60),
@SSN varchar(10)

????Here is where I need it to loop through the personnel records and save them appropriately??????
set @Sql = @Sql + Cast(@UnitID as varchar)
EXEC sp_executesql
@sql,
N'@PositionId int OUTPUT, @Para varchar(4) OUTPUT, @Line varchar(3) OUTPUT, @Posn varchar(60) OUTPUT, @Sid varchar(60) OUTPUT, @name varchar(60) OUTPUT, @ssn varchar(10) OUTPUT',
@PositionId OUTPUT,
@Para OUTPUT,
@Line OUTPUT,
@Posn OUTPUT,
@Sid OUTPUT,
@name OUTPUT,
@ssn OUTPUT

Insert Into tblTask (intAssignedId, intFilterId, intPositionID, strTaskName, dtFound, strSSN, strName, strPositionData, strPersData)
VALUES (@AssignedId, @FilterID, @PositionId, @Taskname, Getdate(), @ssn, @name, @Posn, @Sid)

set @counter = @counter + 1

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources