January 26, 2009 at 4:13 am
Hi all,
Can anyone advise how I would go about putting a stored procedure which returns an integer value into a loop?
I think it should read something like this, but I recieve a syntax error;
DECLARE @Count int
DECLARE process CURSOR FOR
EXEC [dbo].[SyncData] @BatchSize = 1000
OPEN process
FETCH NEXT FROM process
INTO @Count
WHILE @Count > @@FETCH_STATUS
BEGIN
EXEC [dbo].[SyncData] @BatchSize = 1000
END
CLOSE process
DEALLOCATE process
Thanks
January 26, 2009 at 6:24 am
For anyone else who needs to do something similar, I managed to do this by inserting the integer value returned by the stored proc into a temp table and using the cursor to select the value from the temp table;
CREATE TABLE #ProcResults
(
[Count] int
)
INSERT INTO #ProcResults
EXEC [dbo].[SyncData] @BatchSize = 1000
DECLARE @Count int
DECLARE Process CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM #ProcResults
OPEN Process
FETCH NEXT FROM Process
INTO @Count
WHILE @Count > 100
BEGIN
EXEC [dbo].[SyncData] @BatchSize = 1000
END
CLOSE Process
DEALLOCATE Process
January 27, 2009 at 2:58 pm
I have no idea what you are trying to accomplish. If the proc returns an integer value, you can store it into an integer variable similar to calling a function.
create proc dbo.TestProc
@p1 int = 0
as begin
-- Simply return the parameter value less one
declare @Result int;
set @Result = @p1 - 1;
return @Result;
end;-- Procedure
go
-- Now test the procedure
declare @Count int;
set @Count = 10;
while @Count > 0 begin
Print 'The current value is ' + Convert( varchar, @Count );
exec @Count = dbo.TestProc @Count;
end;
Print 'The final value is ' + Convert( varchar, @Count );
But if all it does is return a value, why not make it a function in the first place?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply