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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
1834 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
52326 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
52326 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  
 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.08 seconds. Powered By: Snitz Forums 2000