usp_exec_tsql_strfile

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

CREATE procedure usp_exec_tsql_strfile
       @filepath varchar(256) = null

as

/*******************************************************************/--Name        : usp_exec_tsql_strfile
--Server      : Generic
--Description : Executes a large T-SQL string file locally.
--            : The file must be less than 80,000 charaters. Each
--            : line should be less than 256 characters. Embedded 
--            : 'go's cause problems and are removed before
--            : execution begins.
--
--Variables   : @filepath is the file path of the file containing the 
--              T-SQL code to be executed.
--
--Date        : 07/13/2004
--Author      : Clint Herring
--
--History     : 
--
/*******************************************************************/
-- create a temp holding table.
If (Select object_id('tempdb.dbo.strfile')) > 0 
   Exec ('drop table tempdb.dbo.strfile')
Exec('Create table tempdb.dbo.strfile(id int identity(1,1) not null, script varchar(256) null)')

-- declare some variables (10-8k variables = 80,000 total caharaters)
declare @cnt int
declare @loop int
declare @str0 varchar(8000)
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @str3 varchar(8000)
declare @str4 varchar(8000)
declare @str5 varchar(8000)
declare @str6 varchar(8000)
declare @str7 varchar(8000)
declare @str8 varchar(8000)
declare @str9 varchar(8000)

-- Initializes variables
set @loop = 1
set @str0 = ''
set @str1 = ''
set @str2 = ''
set @str3 = ''
set @str4 = ''
set @str5 = ''
set @str6 = ''
set @str7 = ''
set @str8 = ''
set @str9 = ''

-- Get the file
Set @filepath = 'type ' + @filepath
Insert into tempdb.dbo.strfile
Exec master.dbo.xp_cmdshell @filepath 

--Clear null lines and embedded 'go's
Delete from tempdb.dbo.strfile where script is null or script = 'go'

-- Get the max id corresponding to the last executable line
Select @cnt = max(id) from tempdb.dbo.strfile

-- Build a multi-variable string cmd set (7744 is 8000-256)
While @loop <= @cnt
   Begin
      If datalength(@str8) >= 7744 and datalength(@str9) < 7744
         Select @str9 = @str9 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str7) >= 7744 and datalength(@str8) < 7744
         Select @str8 = @str8 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str6) >= 7744 and datalength(@str7) < 7744
         Select @str7 = @str7 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str5) >= 7744 and datalength(@str6) < 7744
         Select @str6 = @str6 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str4) >= 7744 and datalength(@str5) < 7744
         Select @str5 = @str5 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str3) >= 7744 and datalength(@str4) < 7744
         Select @str3 = @str3 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str2) >= 7744 and datalength(@str3) < 7744
         Select @str3 = @str3 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str1) >= 7744 and datalength(@str2) < 7744
         Select @str2 = @str2 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str0) >= 7744 and datalength(@str1) < 7744
         Select @str1 = @str1 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str0) < 7744
         Select @str0 = @str0 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      Select @loop = min(id) from tempdb.dbo.strfile where id > @loop 
   End
--Print (@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9)  -- for test
--Select * from tempdb.dbo.strfile -- for test

-- Exec the multi-variable string cmd
Exec (@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9)
Drop table tempdb.dbo.strfile
go