sqlblue (10/17/2013)
What is the best way for me to call thisstored proc in batches?
Below is an example of what I think you are trying to do. This proc will keep calling itself until it's done. I am using an arbitrary surrogate key to break the job into batches; the proc will execute (<#of rows to update> /@row_end) times.
Here is some sample data:
-- (1) Let's create some sample data
USE tempdb
GO
IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable;
CREATE TABLE #sometable(some_id int primary key, some_value varchar(40), some_date date);
WITH iTally(n) AS
(SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT (0)))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #sometable
SELECT n, 'old value', getdate()-(2500+floor(2500 * RAND(convert(varbinary, newid()))))
FROM iTally
GO
--BEFORE
SELECT * FROM #sometable
ORDER BY some_id
GO
Here is a recursive stored proc with a couple examples:
-- (2) The recursive update stored proc
CREATE PROC someproc(@row_start int=0, @row_end int=10000, @new_value varchar(100)='new value')
AS
SET NOCOUNT ON;
WITH surrogate(s_key) AS
(SELECT ROW_NUMBER() OVER (ORDER BY some_id)
FROM #sometable)
UPDATE #sometable
SET some_value=@new_value
WHERE some_id>@row_start AND some_id<=@row_start+@row_end;
SELECT @row_start=@row_start+@row_end;
IF @row_start<(SELECT COUNT(*) FROM #sometable)
EXEC someproc @row_start,@row_end,@new_value;
GO
--using the defaults
EXEC someproc
--50,000 rows
EXEC someproc 0,50000,'blah, blah'
Perhaps this will help.
P.S. I came up with this in a few minutes during lunch and don't don't know if this is the best way to accomplish this or not (comments, criticism welcome).
EDIT: my stored proc had a couple issues. All fixed.
-- Itzik Ben-Gan 2001