help with executing procedure in batches

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with executing procedure in batches

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-04-28 : 12:21:50

How can I re-write/modify this code to execute the first 100 records from the view then continue onto the next 100, etc etc.

The problem I am running into is that the @ITNBR has a 8000 chars limit and can't hold the records from my view.


USE [ItemMaster]
GO
Declare @ITNBR varchar(8000)
 SELECT @ITNBR = COALESCE(@ITNBR + '; ', '') + 'exec b1 '''+ 
        RTRIM(CAST(ITNBR AS varchar(15))) + ''''
 FROM [dbo].[vw_itemasa_9]

exec(@ITNBR)

Thanks,

JLM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-28 : 12:44:30

Check whether this article helps u

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Srinika

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-28 : 12:53:25

There are a lot of ways to do this. Here's one way. You'll obviously need to adjust the specifics for your view.


set nocount on
declare @s varchar(8000)
	,@id int

set rowcount 10
select @id = 0

while @@rowcount > 0
begin
	--display current 10 values
	print @s

	--reset
	select @s = null

	--Get next set
	select	@s = coalesce(@s + ', ' + convert(varchar, id), convert(varchar, id))
		,@id = id
	from	sysobjects
	where	id > @id
	order by id	
end

--don't forget to do this!
set rowcount 0


-----------------------------------------------------------------------
output:
1, 2, 3, 4, 6, 8, 9, 10, 11, 12
14, 19, 20, 21, 22, 23, 24, 95, 96, 226815870
242815927, 278292051, 326292222, 370816383, 402816497, 418816554, 470292735, 518292906, 539864990, 555865047
571865104, 587865161, 603865218, 619865275, 635865332, 820913996, 836914053, 852914110, 868914167, 900914281
916914338, 932914395, 948914452, 964914509, 1030294730, 1046294787, 1062294844, 1078294901, 1094294958, 1110295015
1126295072, 1142295129, 1158295186, 1174295243, 1190295300, 1206295357, 1222295414, 1238295471, 1254295528, 1270295585
1275151588, 1286295642, 1291151645, 1302295699, 1307867726, 1318295756, 1332915820, 1334295813, 1339151816, 1348915877
1350295870, 1355151873, 1364915934, 1366295927, 1371151930, 1380915991, 1382295984, 1387151987, 1396916048, 1398296041
1412916105, 1414296098, 1428916162, 1430296155, 1444916219, 1446296212, 1460916276, 1462296269, 1476916333, 1478296326
1492916390, 1494296383, 1510296440, 1526296497, 1528392514, 1542296554, 1544392571, 1556200594, 1566628624, 1572200651
1604916789, 1620916846, 1672393027, 1686297067, 1717581157, 1718297181, 1734297238, 1750297295, 1766297352, 1782297409
1798297466, 1814297523, 1830297580, 1846297637, 1862297694, 1945057965, 1961058022, 1977058079, 1993058136, 2009058193
2025058250, 2041058307, 2056394395, 2073058421, 2089058478

EDIT:
if you use the "while @@rowcount > 0" condition, you need to be carefull about what the last statement is before the "END". Notice I put the display and reset statements before the SELECT so that @@rowcount was checking the important statement.

Be One with the Optimizer
TG

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-04-28 : 16:36:01

Thanks TG, it helped me a bit but I couldn't get the code to work
as I had expected.

When I modified the code for the view that I have and the 'Exec'
statement, it will only excute the first round but doesn't loop
through the rest of the records. When I remove the 'Exec' statement,
it does display all the records per the rowcount that is set. I'm
just not sure why it doesn't finish the loop when the 'Exec' statement
is in there. =(


set nocount on
declare @s varchar(8000)
	,@id int

set rowcount 20
select @id = 0

while @@rowcount > 0
begin
	--display current 10 values
	print @s

	--reset
	select @s = null

	--Get next set
	select	@s = coalesce(@s + '; ', '') + 'exec b1 '''+ RTRIM(CAST(ITNBR AS varchar(15))) 
		+ '''', @id = id
	from	[dbo].[vw_itemasa_9_id]
	where	id > @id
	order by id	
  exec(@s)
end
-- Clear rowcount
set rowcount 0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-28 : 17:26:20

Did you notice my EDIT from the first post. That explains the problem.
Try this:


set nocount on
declare @s varchar(8000)
	,@id int

set rowcount 20

--Get first set
select	@s = coalesce(@s + '; ', '') + 'exec b1 '''+ RTRIM(CAST(ITNBR AS varchar(15))) 
	+ '''', @id = id
from	[dbo].[vw_itemasa_9_id]
where	id > @id
order by id	


while @@rowcount > 0
begin
	--display current 10 values
	print @s
	exec(@s)

	--reset
	select @s = null

	--Get next set
	select	@s = coalesce(@s + '; ', '') + 'exec b1 '''+ RTRIM(CAST(ITNBR AS varchar(15))) 
		+ '''', @id = id
	from	[dbo].[vw_itemasa_9_id]
	where	id > @id
	order by id	
--  exec(@s)
end

-- set rowcount back to unlimitted
set rowcount 0

Be One with the Optimizer
TG

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-04-28 : 18:12:19

Thanks TG, that worked!

Had to add 'select @id = 0' in your last post
as it was left out and didn't work without.

JLM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources