JUST FOR FUN!

Here's a possibility, but its really rube goldberg like, it would probably be easier with actual programming tools like dot.net or powerscript. The included method is probably low on my list of recommendations because you have programs writing other programs and then executing them within the same programming, you can really get some confusing interactions, so obviously you'd like to test stuff like crazy (and also question why the heck you aren't using better tools and designs :w00t:)

In the example included, I use sqlcmd from within a batch file to call sql that writes another batch file which the first one then executes.

I made a directory, called it u:\testdir1 (I have a drive u:) Also assume I have an sql server instance called mysqlserver\dev and in that server I have a database called 'dev'.

In this directory I have:

testdrive1.bat

SQLCMD -m-1 -h-1 -E -S mysqlserver\dev -d %1 -i %2 -v tbname=%3 -o %4

testdrive1.sql

set nocount on

SELECT 'CALL U:\TESTDIR1\TESTDRIVE1.BAT ' +

(SELECT PARAM1 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM2 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM3 FROM TESTPARAMS1) + ' ' +

(SELECT PARAM4 FROM TESTPARAMS1)

Inside of mysqlserver\dev's database also called dev I have a table:

create table testparams1

(

param1 varchar(100),

param2 varchar(100),

param3 varchar(100),

param4 varchar(100)

)

Inside of that table I have

param1param2param3param4

devU:\TESTDIR1\testscript1.sqltesttable1U:\TESTDIR1\testout1.txt

Obviously I only have one row in my table 'testparams1', so it makes selecting the row of parameters I want simple.

I also have a file called testscript1.sql which is just an example of a script I would like to call as a result of its name being a parameter inside table 'testparams1'.

select * from $(tbname)

I run it all with:

u:\testdir1\test_it_all.bat

CALL U:\TESTDIR1\TESTDRIVE1.BAT DEV U:\TESTDIR1\TESTPARAMS1.SQL NONE U:\TESTDIR1\STEP2.BAT

CALL U:\TESTDIR1\STEP2.BAT

and I then have a result in file u:\testdir\testout1.txt because that file name is what I had in column param4 in table 'testparams1.'

I don't know the wisdom of doing things this way however, but I have at least one blob of scripting that works in a similar fashion and its been running for quite a while. Since it writes and then executes a batch file, its definitely not a reentrant procedure suitable for multiuser applications LOL. Obviously testing and understanding the workflow should be top priority and additionally, you probably need a pretty darn good reason to be doing this sort of coding in the first place.

edit: forgot to make the target script use a parameter passed with -v and also hardwired path for consistency