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:
SQLCMD -m-1 -h-1 -E -S mysqlserver\dev -d %1 -i %2 -v tbname=%3 -o %4
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
Inside of that table I have
param1 param2 param3 param4
dev U:\TESTDIR1\testscript1.sql testtable1 U:\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:
CALL U:\TESTDIR1\TESTDRIVE1.BAT DEV U:\TESTDIR1\TESTPARAMS1.SQL NONE 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
to properly post on a forum:http://www.sqlservercentral.com/articles/61537/