drop proc batch_tsql
go
create proc batch_tsql 
	@tsql_table varchar(255) ,
	@output_table varchar(30)=null
as

-- -----------------------------------------------------------------------------
--
-- Object Name:		batch_tsql
-- Author:		AENDER
-- Created:		04Aug98
--
-- Description:		Execute TSQL script held in table
--
-- Parameters:		
--			The procedure is passed 2 table names
--
--			@tsql_table
--				Must contain a column names tsql
--				tsql can contain a tsql script with as many statements 
--				as required. Including USE statements.
--
--			@output_table
--				If you want to process the output from your script
--				you can supply an output table which must contain a
--				column called output.
--				NOTE: output will be truncated to 255 bytes
--
--
-- Return Codes:	none
--  			
-- History:
--			Date	Name	Version	Description
--
-- ----------------------------------------------------------------------------- 

SET NOCOUNT ON

-- ----------------------------------------------------------------------------- 
-- Create a unique table name with @@spid in it
-- ----------------------------------------------------------------------------- 

DECLARE @tname varchar(30)	SELECT @tname="##BTSQL" + convert(varchar(8),@@spid)
EXEC	('IF EXISTS (SELECT * from tempdb..sysobjects where name="' + @tname +'") DROP TABLE ' + @tname )

-- ----------------------------------------------------------------------------- 
-- Create a file name with @@spid in it - Assumes server has share called TEMP
-- ----------------------------------------------------------------------------- 

DECLARE @fname varchar(255)	SELECT @fname='\\' +@@servername+'\temp\BTSQL'+convert(varchar(8),@@spid)+'.iql'

DECLARE	@cmd varchar(255)

-- ----------------------------------------------------------------------------- 
-- EXTRACT requesters TSQL
-- ----------------------------------------------------------------------------- 

EXEC	('CREATE TABLE ' + @tname + '(tsql varchar(255))')
EXEC	('INSERT INTO ' + @tname + '(tsql) select tsql from ' + @tsql_table)

-- ----------------------------------------------------------------------------- 
-- Put TSQL to FILE
-- ----------------------------------------------------------------------------- 

SELECT	@cmd='BCP tempdb..' + @tname + ' out ' + @fname + ' /Usa /P /c '
EXEC	master..xp_cmdshell @cmd,no_output

-- ----------------------------------------------------------------------------- 
-- Run ISQL ith TSQL as input
-- ----------------------------------------------------------------------------- 

SELECT	@cmd = 'ISQL /Usa /P /n /w4095 /HBatch_TSQL /i'+@fname
IF	@output_table=null
	EXEC	master..xp_cmdshell @cmd,no_output
  ELSE
	EXEC	('INSERT INTO '+ @output_table + ' (output)  EXEC	master..xp_cmdshell "'+  @cmd +'"')

GO

-- ----------------------------------------------------------------------------- 
-- TEST IT
-- ----------------------------------------------------------------------------- 

drop table #t1
drop table #t2
go

create	table #t1 (id int identity,tsql varchar(255), output char(10) null)
create	table #t2 (a char(1) null ,output varchar(255) null)

insert	into #t1 values ('SET NOCOUNT ON','dumX')
insert	into #t1 values ('exec master..xp_cmdshell "net send aender ts1",no_output','dum1')
insert	into #t1 values ('exec master..xp_cmdshell "net send aender ts2",no_output','dum1')
insert	into #t1 values ('exec sp_who','dumX')
EXEC	batch_tsql #t1  ,#t2
select	output from #t2