how-to generate sql stored procedures with bcp and stored them into separate files

URL

SET NOCOUNT ON;

DECLARE @DirForOutput NVARCHAR(300)

-- THIS IS THE DIRECTORY WHERE THE GENERATED CODE WILL BE SAVED 

SET @DirForOutput = 'D:\cas\Sql_dev\sql_dev.0.3.2.20100509\Scripts\TestDataTypes\output\StoredProcs\Insert\'

DROP TABLE #TableList 

--Grab all the functions for the current DB 

SELECT IDENTITY(INT,1,1) AS ID,

NAME

INTO #TableList

FROM sys.tables where name like 'TestDataType%'

DECLARE

@Loopid INT,

@MaxId INT

--Grab start and end values for the loop

SELECT @Loopid = 1,

@MaxId = MAX(ID)

FROM #TableList

DECLARE

@TableName VARCHAR(400) ,

@Permission VARCHAR(20),

@DataType VARCHAR(20)

--This is where the loop starts

WHILE @Loopid <= @MaxId BEGIN

--grab the function name and type

SELECT @TableName = NAME

FROM #TableList

WHERE ID = @Loopid

DECLARE @StrSqlCode1 NVARCHAR(max)

--construct the statement

-- ACTION !!! boom boom boom db.schema.procToCall format !!!

select @StrSqlCode1 = 'exec cas_dev.dbo.procUtils_GenerateInsertProc ''' + @TableName + ''''

--PRINT (@StrSqlCode1) --change PRINT to EXECUTE if you want it to run automatically

exec sp_executesql  @StrSqlCode1

declare @bcpsql varchar(8000)

-- boom boom boom check the path !!!

select @bcpsql = 'BCP "' + @StrSqlCode1+ '" queryout ' + @DirForOutput + 'procGen_' + @TableName + '_Insert.StoredProcedure.sql -c -t, -T '

--print @bcpsql

exec master..xp_cmdshell @bcpsql

--increment counter

SET @Loopid = @Loopid + 1

END

--clean up

DROP TABLE #TableList