dcsimg

Code Example 1:

USE master
GO

BEGIN TRANSACTION T_sp__execAllInTable_SDP
go


print 'sp__execAllInTable'
go
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from sysobjects where id = object_id(N'[sp__execAllInTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp__execAllInTable]
GO


/***********************************************************
Copyright  2000 SQL Dev Pros, Inc.
This code may be freely copied and distributed as long as
this header remains first and is not modified, and any
modifications are noted in the MODIFICATIONS header below.

Visit SQL Dev Pros at www.sqldevpro.com for great SQL
Server tools and consulting.

Try SQL Dev Pro: A utility for Schema and Data Compare,
Data Extract, Search, Export, Script, SourceSafe, Recompile
and more for MS SQL Server. It's FREE! Check it out at
www.sqldevpro.com.

Created by Aaron Goldman, 4/10/2000
general release version 1 - 1/3/2001

DESCRIPTION:

assumes existence of a table:

#ExecMe(stmt varchar(8000))

will exec each stmt in the table


IMPLEMENTATION NOTES:


*/
/**************************************************
MODIFICATIONS:
Date Author Comment/details
5/3/01 AG shows time stats

**************************************************/
CREATE Procedure sp__execAllInTable

@bMsgs bit = 0,
@showTimeStats bit = 0

As
/* set nocount on */

declare @stmt varchar(8000)
declare @startTime datetime, @sTemp as varchar(8000)

/* set nocount on */
declare #execMe_cursor cursor LOCAL STATIC
for select stmt
from #execMe
open #execMe_cursor
fetch next
from #execMe_cursor
into @stmt
while (@@fetch_status = 0)
begin
/* execute logic */
if @bMsgs = 1
begin
print ''
print @stmt
end

select @startTime = getdate()

exec(@stmt)

if @showTimeStats = 1
BEGIN
select @sTemp = cast(DATEDIFF(ms, @startTime, getdate()) as varchar) + ' ms elapsed'
print @sTemp
END

fetch next
from #execMe_cursor
into @stmt
end
close #execMe_cursor
deallocate #execMe_cursor


return 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


IF OBJECT_ID('sp__execAllInTable') IS NULL 
BEGIN 
Rollback Transaction
print 'msg rolled back ' 
print '' 
END
ELSE
BEGIN
COMMIT TRANSACTION
END
go