March 16, 2010 at 1:19 pm
How to loop through buffer table and pass each record value to SP?
March 16, 2010 at 1:30 pm
Are you wanting to execute the stored procedure for each row in the table?
I'd rather stick needles in my eyes, that will be agonizingly slow.
Or are you trying to pass the contents of the table as an argument to the procedure in a single execute?
March 16, 2010 at 1:36 pm
I want to execute the stored procedure for each row in the table. Dont worry about the time the table is small
March 16, 2010 at 2:32 pm
Please explain a little more detailed what you're trying to do. Especially in terms of the stored procedure.
It might be the case you're trying to do a row-based approach where a set-based solution would perform much better...
March 16, 2010 at 2:45 pm
DECLARE @Col1 int, @Col2 int, @Col3 varchar(10)
DECLARE Molases CURSOR FOR
SELECT Col1, Col2, Col3 FROM BufferTable
OPEN Molasis
FETCH NEXT FROM Molases INTO @Col1, @Col2, @Col3
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spProcessMolases @Col1, @Col2, @Col3
FETCH NEXT FROM Molases INTO @Col1, @Col2, @Col3
END
CLOSE Molases
DEALLOCATE Molases
March 17, 2010 at 7:00 am
grasshopper26 (3/16/2010)
I want to execute the stored procedure for each row in the table. Dont worry about the time the table is small
Maybe now but who knows what the future holds for this table. If you can provide the stored proc code, I'm sure there is a much better solution than a cursor, although Steve's suggestion would work.
mmpph - Steve said "Molases". Reminds me of an old joke:
A father, mother and baby mole woke up after a long winter. The father was the first to the hole and said "Ah, smell the sping in the air!". Mother was next and said "Ah, smell the sping in the air!". Baby came last and said "I don't smell nothing but some mole-asses". 😀
Just a little (very little!) corn-ball humor to jump start the day!
-- You can't be late until you show up.
March 19, 2010 at 11:34 am
--> This can be quite efficient in an old linear style way of getting things done.
--> But its always better to understand your set theory
DECLARE @Kitty TABLE (RowID INT Identity(1,1), Cat VARCHAR(20))
INSERT INTO @Kitty(SomeData)
SELECT 'Pete'
INSERT INTO @Kitty(SomeData)
SELECT 'Brian'
INSERT INTO @Kitty(SomeData)
SELECT 'Brian
--> declare vars
DECLARE @Row INT
DECLARE @MaxRow INT
DECLARE @CatName VARCHAR(20)
SET @Row = 1
SET @MaxRow = (SELECT MAX(RowID) FROM @Kitty) + 1
WHILE @Row < @MaxRow
BEGIN
SELECT @CatName = (SELECT CatName FROM @Kitty WHERE RowID = @Row)
EXEC usp_FireOverCatName @CatName
SET @Row = @Row + 1
END
March 20, 2010 at 2:54 pm
I am sure the cursor will be just fine here (though it would have been nice to see a non-default cursor!)
Anyway, not looking to start that debate, here is a link to passing stuff around that the OP might find useful one day:
http://www.sommarskog.se/arrays-in-sql.html
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 22, 2010 at 5:38 am
--> the first way I was shown which reminds me of looping with a modulus in c
DECLARE @CatName VARCHAR(20)
DECLARE @Kitty TABLE (CatName VARCHAR(20),Processed INT)
INSERT INTO @Kitty(CatName,Processed)
SELECT 'Pete',0
INSERT INTO @Kitty(CatName,Processed)
SELECT 'Brian',0
INSERT INTO @Kitty(CatName,Processed)
SELECT 'Brianetta',0
SELECT TOP 1 @CatName = CatName FROM @Kitty WHERE Processed = 0
WHILE @@ROWCOUNT != 0
BEGIN
EXEC usp_CallCat @CatName
UPDATE @Kitty SET Processed = 1 WHERE CatName = @CatName
SELECT TOP 1 @CatName = CatName FROM @Kitty WHERE Processed = 0
END
March 22, 2010 at 7:16 am
Just a quick point. In all the tests I have ever seen, there is essentially no performance difference between a correctly-written cursor, and any of the WHILE loop methods.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 3:43 am
I agree with you there performance wise. I have benchmarked queries and it's swings and roundabouts. We have had more memory issues with cursors in 2005 than in 2000 though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply