RUNSPS

RUNSPS reads an Excel file that contains definitions of stored procedures and calls to these procedures. RUNSPS is intended to be a simple test tool.

Command-line syntax:

runsps [-Server server] [-User user] [-Password passw] [-database db]
       [-nolog] [-abort] [-noexec]  file

`-Server`	Which server to run in.
`-User`	Which user to run as. In difference to other AbaPerls tools, the default is the current user, not sa. `-User` only applies when you also specify `-Password`.
`-Password`	The password. If you do not specify `-Password`, RUNSPS uses Windows authentication and logs in with your Windows user. Specify `-P ""` if you have a blank password.
`-database`	The database to run in. The default is tempdb. (RUNSPS overrides the default db for the user.)
`-nolog`	Normally, RUNSPS prints all the calls to the stored procedures it makes. You can turn this off with `-nolog`. If you like to save the calls as an SQL script, redirect output with > on the command line.
`-noexec`	Instructs RUNSPS to only generate the calls, but not run them. You still need to supply correct login information, as RUNSPS needs to interact with the database to get parameter information.
`-abort`	Specify `-abort` to instruct RUNSPS to abort if a stored-procedure call raises an error, or the return status is <> 0. If you do not specify `-abort`, RUNSPS will continue in case of an error.

The first column on a row, holds the name of the stored procedure to be defined or invoked. The simple rule is: on the first row where procedure name appears it is a defintion, remaining rows are invocations.

If the first column is blank, RUNSPS assumes the row is a call to the same stored procedure that was defined/invoked on the previous line. If the value in the column starts with #, the entire line is a comment and will be ignored by RUNSPS. In place of a procedure name you may specify BEGIN/COMMIT/ROLLBACK TRANSACTION to get transaction handling.

Remaining cells on the row are parameters. On a definition row, you enter the parameter name, with or with theleading @. You don't need to list parameters that you don't intend to use. The parameters may come in any order.

If RUNSPS finds an entirely empty row, that is no procedure name, nor any parameter values, RUNSPS stops reading the Excel book.

On an invocation row, the cells hold the values to pass to the stored procedure. Leave a cell empty to pass the default value for the parameter. To explicitly pass NULL, write the string NULL. (Must be all-uppercase; "Null", "null" etc is interpreted by RUNSPS as the corresponding string value.) Do not specify any string delimiters around strings or dates, RUNSPS takes care of this detail. Use the format YYYY-MM-DD to specify dates.

In place of values you can specify variables to make it possible to bring the value of an output parameter with you to the next procedure to call. Variable names start with the general currency sign (¤), also known as the "sun character". (International users who do not have this character available on their keyboards, can enter the character by pressing down the ALT key and enter 0164 on the numeric keypad.) In the output from RUNSPS you will not see the variable names, but only the values they had at the respective call.

If a stored procedure produces one or more result sets, RUNSPS prints these on a simple format.

There is a very simple example in this file. To be able to run the example you need create these two procedures in tempdb:

CREATE PROCEDURE silly_walk_1 @date datetime,
                             @id   int OUTPUT AS
   SELECT @id = id
   FROM   sysobjects o1
   WHERE  crdate = (SELECT MIN(crdate)
                    FROM   sysobjects o2
                    WHERE  o2.crdate > @date)
go
CREATE PROCEDURE silly_walk_2 @id int AS
   SELECT * FROM syscolumns WHERE id = @id
go

To run the example, say:

runsps -U user -P pwd -S server runsps-example.csv