SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Create loop to update several tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 12/18/2012 :  15:13:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 12/18/2012 :  19:49:31  Show Profile  Reply with Quote
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!

Edited by - Bustaz Kool on 12/19/2012 16:24:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 12/19/2012 :  01:32:36  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 12/19/2012 :  11:22:56  Show Profile  Reply with Quote
Thanks for the help, works great.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 12/19/2012 :  22:08:06  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000