Store data horizontally or vertically?

[serge]

My project is to automate testing of Stored Procedures
of type SELECT (at least for now).

I want to create a table where each stored procedure's
input parameter values are entered and in another table
the expected result value(s) are entered when executed
against a sample database containing manually entered
and verified data.

My current problem is that the stored procedures' input
parameters range from none to 50 parameters (ok now
that I think of it maybe this SP with 50 parameters is an
INSERT SP; regardless let's assume I have SPs that
require 10-20-30+ parameters).

My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Now I'm still in research mode and I haven't decided on any choice
yet. And I know each approach has serious consequences; let's just
say they are all with some limitations.

Has anyone dealt with this scenario before? This is the first time I
need a table to hold various types of returned data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.

What would you suggest?

Thank you

[Steve Jorgensen]

On Thu, 10 Nov 2005 23:33:16 -0500, "serge" <se****@nospam. ehmail.com> wrote:

My project is to automate testing of Stored Procedures
of type SELECT (at least for now).

I want to create a table where each stored procedure's
input parameter values are entered and in another table
the expected result value(s) are entered when executed
against a sample database containing manually entered
and verified data.

My current problem is that the stored procedures' input
parameters range from none to 50 parameters (ok now
that I think of it maybe this SP with 50 parameters is an
INSERT SP; regardless let's assume I have SPs that
require 10-20-30+ parameters).

My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Now I'm still in research mode and I haven't decided on any choice
yet. And I know each approach has serious consequences; let's just
say they are all with some limitations.

Has anyone dealt with this scenario before? This is the first time I
need a table to hold various types of returned data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.

What would you suggest?

Thank you

Clearly, if you're going to use relational db tables to store the data, the
benefits of 1-m relationships outweigh the benefits of n-columns. That said,
when you're a hammer, every problem looks like a nail, and I'm not sure that
database tables are the best medium for storing your tests or results.

Check out fit.c2.com. Notice that there is a .NET implementation of FIT, that
should work very nicely as a platform to create FIT tests for MS SQL Server
stored procedures, though JDBC from Java would probably work fine, too.

[Erland Sommarskog]

serge (se****@nospam. ehmail.com) writes:

My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.

At the one extreme, one does a full-fledged data model with a table
store_procedure s that defines the procedure as such. Then there is a
sub-table that defines the output columns, and then there is one that
holds with rows with output data. Note here that there would be one row for
each value. That is, if an SP returns 20 columns, one row in the result
set results in 20 rows in this table. It will be akweard to do this
transposing. For the input data you would have a similar arrangement,
although somewhat simpler, as parameters do not come in rows.

The other extreme is to say that each procedure is an object of its own,
and thus needs a table of its own. Here output rows can be stored as
rows. But with the number of tables you indicate, this will be quite
unmanagable. Then again, while the first extreme has fewer tables, there
is still the same amount of data to manage.

What I don't like is some sort of a middle ground as you indicate in
your first alternative. Somewhere you will need to store that colunm 25
for procedure A has the data type datetime. Then again, this is probably
the model that makes the test engine easiest to implement.

Steve indicated in his reply that a relational database may not be the
best data store for this situation. And maybe he is right. One solutinn
that has some appeal to me is to use XML. Each XML document would then be
the output from a stored procedure. Parameters could be handled in the same
way, but regular table storage may still work for them, as they have one
dimension less.

In SQL 2005 there is a native xml data type which simplifies this
implementation, but you coudl use ntext in SQL 2000 for the XML
docuemnts. You would have to get all data to client level for comparisons
though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx