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.
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, 2263This 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 variablesDeclare @RecId as integer, @IssueNum as integer, @Count int;Create table #TempIssue (IssueId int); --Insert the temp variables into a tableInsert #TempIssue (IssueId)SELECT value FROM dbo.ParmsToList(@IssueId, ',') Select @Count = COUNT(*) from #TempIssueSelect @IssueNum = IssueId from #TempIssueWhile @@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 LogSelect @RecId = intRecId from Drat_Issued where intIssuedID = @IssueNum; --Mark records with Transfer IN effectUpdate 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 HolderInsert 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 loop3) 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! |
|
|
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 variablesDeclare @RecId as integer, @IssueNum as integer, @Count int;Create table #TempIssue (IssueId int);--Insert the temp variables into a tableInsert #TempIssue (IssueId)SELECT value FROM dbo.ParmsToList(@IssueId, ',') --Insert into the transfer DatabaseInsert Drat_Reissue (intIssuedID, intReIssueby, intReIssueTo, dtIssue)SELECT IssueId, @IssuedBy, @IssueTo, GETDATE())FROM #TempIssue--Mark records with Transfer IN effectUpdate di set bitTransfer = 1FROM Drat_Issued diINNER JOIN #TempIssue tON t.IssueID = di.intIssuedId Update drset intTransTypeId = 6FROM Drat_Received drinner join Drat_Issued diON di.intRecId = dr.intRecId INNER JOIN #TempIssue tON t.IssueID = di.intIssuedId --Insert transaction that product was ReIssued to someone other than receipt HolderInsert Drat_Transactions (intTransTypeId, intRecId, dtTransaction, intTransactionBy, intTransactionFrom)SELECT 6, di.intRecId , getdate(), @IssuedBy, @IssueToFROM Drat_Issued diINNER JOIN #TempIssue tON t.IssueID = di.intIssuedId ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2012-12-19 : 11:22:56
|
Thanks for the help, works great. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 22:08:06
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|