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 2008 Forums
 Transact-SQL (2008)
 Create loop to update several tables

Author  Topic 

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2012-12-18 : 15:13:55
What got a fucntion to tear apart a string of values to use to update, and insert into tables but the problem I am having is I only get the last value everytime and it loops through last records indefinetly. How can I get all values to update properly.

Here is a sample data.
Execute sp_CreateTransfer '10238,10242,10243', 2825, 2263

This is my proc.
1st I try to get all the values of string into a temp table.
2nd want to loop through all values I put into temp table to get IssuedNum to update or insert various tables.

--Declare all variables
Declare @RecId as integer, @IssueNum as integer, @Count int;
Create table #TempIssue (IssueId int);

--Insert the temp variables into a table
Insert #TempIssue (IssueId)
SELECT value FROM dbo.ParmsToList(@IssueId, ',')

Select @Count = COUNT(*) from #TempIssue
Select @IssueNum = IssueId from #TempIssue
While @@ROWCOUNT <= @Count
BEGIN
--Insert into the transfer Database
Insert Drat_Reissue (intIssuedID, intReIssueby, intReIssueTo, dtIssue)
Values
(@IssueNum, @IssuedBy, @IssueTo, GETDATE())

-- Get Information to put in the Transaction Log
Select @RecId = intRecId from Drat_Issued where intIssuedID = @IssueNum;

--Mark records with Transfer IN effect
Update Drat_Issued set bitTransfer = 1 where intIssuedId = @IssueNum;
Update Drat_Received set intTransTypeId = 6 where intRecId = @RecId;

--Insert transaction that product was ReIssued to someone other than receipt Holder
Insert Drat_Transactions (intTransTypeId, intRecId, dtTransaction, intTransactionBy, intTransactionFrom)
VALUES
(6, @RecId, getdate(), @IssuedBy, @IssueTo)
END

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-18 : 19:49:31
1) "Select @IssueNum = IssueId from #TempIssue" - You are selecting one record out of the entire table but not specifying which one you want: MIN(IssueId)? The SQL Server is pseudo-randomly picking the last one.
2) You are not changing @IssueNum within the loop
3) The while condition (@@ROWCOUNT <= @Count) will not be met so you have an infinite loop. Perhaps you wanted to compare against a running total of the records processed. You would need a variable created, initialized and incremented appropriately.


=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 01:32:36
why do you need a loop here? you may be better off applying set based solution like


--Declare all variables
Declare @RecId as integer, @IssueNum as integer, @Count int;
Create table #TempIssue (IssueId int);

--Insert the temp variables into a table
Insert #TempIssue (IssueId)
SELECT value FROM dbo.ParmsToList(@IssueId, ',')


--Insert into the transfer Database
Insert Drat_Reissue (intIssuedID, intReIssueby, intReIssueTo, dtIssue)
SELECT IssueId, @IssuedBy, @IssueTo, GETDATE())
FROM #TempIssue


--Mark records with Transfer IN effect
Update di
set bitTransfer = 1
FROM Drat_Issued di
INNER JOIN #TempIssue t
ON t.IssueID = di.intIssuedId

Update dr
set intTransTypeId = 6
FROM Drat_Received dr
inner join Drat_Issued di
ON di.intRecId = dr.intRecId
INNER JOIN #TempIssue t
ON t.IssueID = di.intIssuedId


--Insert transaction that product was ReIssued to someone other than receipt Holder
Insert Drat_Transactions (intTransTypeId, intRecId, dtTransaction, intTransactionBy, intTransactionFrom)
SELECT 6, di.intRecId , getdate(), @IssuedBy, @IssueTo
FROM Drat_Issued di
INNER JOIN #TempIssue t
ON t.IssueID = di.intIssuedId


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2012-12-19 : 11:22:56
Thanks for the help, works great.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 22:08:06
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -