Thread: Problem with cursors
10-23-11, 22:03 #1Registered User
- Join Date
- Oct 2011
Unanswered: Problem with cursors
I have table like this:
id number value
1 300 233
2 343 434
2 565 655
3 562 343
1 434 232
3 232 444
3 458 232
It have to be
id number:value, number:value...
1 300:233, 434:232
2 343:434, 565:655
and so on
Basically, I have to write stored procedure to merge 2nd and 3rd column and group for every ID.
What I did is CAST, and I got "merged" 2nd and 3rd column, and now I need to group id by id, for unknown number of ids (cant do id manualy).
So, instead of original 3-column table, I made new one with 2 rows
Just need somehow to group it. I'm sure it can be done with cursor(s), but I can get to it.
Thanks in advance for help.
Last edited by el ninho; 10-23-11 at 22:07.
10-26-11, 22:29 #2Registered User
- Join Date
- Mar 2009
Avoid cursors wherever possible - use a function to generate a string based on the ID...
CREATE function [dbo].[get_value_list_thing] (@ID Int) RETURNS nvarchar(512) AS Begin Declare @v_ValueList nvarchar(512) = Null Select @v_ValueList = Case When @v_ValueList Is Null Then '' Else @v_ValueList + ', ' End + cast(Number as nvarchar) + ':'+cast(Value as nvarchar) From YourTable Where ID = @ID ORDER by Number RETURN @v_ValueList END GO select distinct id,dbo.get_value_list_thing(ID) from yourtable
10-30-11, 07:58 #3Registered User
- Join Date
- Sep 2011
take a look to below idea and apply it on your Code
he trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).
INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
SELECT OT.OrderTransactionId, P.MediaTypeId
FROM #OrderTransaction OT WITH (NOLOCK)
INNER JOIN #Product P WITH (NOLOCK)
ON OT.ProductId = P.ProductId
JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
--WHERE some optional conditions
Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.
I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.
performance - SQL Server: Improve PROCEDURE without using CURSOR - Stack Overflow