Automated way to discover the structure of the result set of a stored procedure?
in [Server Configuration]
|
Prev: Simple recovery database bloating?
Next: Automated way to discover the structure of the result set ofa stored procedure?
From: Behzad Sadeghi on 25 Jul 2010 07:46 Is it possible to write a script that, as its input, takes the name of a stored procedure, and, as its output, produces a table each of whose rows contains the name and data type of each column in the (presumably unique) result set of that stored procedure? I am trying to build a system that can create a set of scripts that can act as a test harness for a given stored procedure. I would like to automate as much of this task as possible. Currently, I am generating scripts that can read sets of parameters stored in a table, invoke the stored procedure for each parameter set (represented by each row), and collectively store the result sets returned by each invocation of the procedure into another table, for comparison with previous or future runs of the same tests. To generate the tables which store the results, I need to know the structure of the procedure's result sets. But, as of now, I cannot think of a way to do this, except for visually inspecting the code of the stored procedure and manually constructing a definition for the result set. Can anyone help? Behzad
From: Erland Sommarskog on 25 Jul 2010 11:45 Behzad Sadeghi (behzad.sadeghi(a)gmail.com) writes: > Is it possible to write a script that, as its input, takes the name of > a stored procedure, and, as its output, produces a table each of whose > rows contains the name and data type of each column in the (presumably > unique) result set of that stored procedure? For the general case, the answer is: no. A stored procedure can theoretically generate a diffrently structured result set every time. Nevertheless, there are many situations where client API needs to be able to do this. As just one example, consider a query that uses OPENQUERY(SERVER, 'EXEC that_sp'). For the local server to be able to compile the query, it needs to determine the result set of the remote query. The mechanism used today is to issue the command SET FMTONLY ON. This sets a kind of half-exec mode where SQL Server runs through all statements, but do not actually run queries, but returns empty result sets with the metadata. This method works so-so. There are a lot of situations where it fails. The prime example is a procedure that creates a temp table, and the performs action against this temp table. Since the table does not get created in FMTONLY mode, queries that refers to the temp table fails. Another potential problem is that in FMTONLY conditions for IF and WHILE are no honored, but all branches are executed. A recursive stored procedure will fail in FMTONLY mode, because of infinite recursion. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Behzad Sadeghi on 25 Jul 2010 13:57 Thank you, Erland. Reading your answer, I realized I have another problem. Let's say I run the stored procedure from within a T-SQL script, either using FMTONLY or not. I will get a result set, either empty or not. Now, how can I examine this result set to decipher its columnar structure from within my T-SQL script? I know I can use "insert <myTable> exec <myProc>", which will persist a table in my database, which I can then examine using system catalog views. But for that, I already need to know the structure of the result set. Is there a way to do this (persist the result set to the database) without knowing its structure. Again, I need to do all this from within T-SQL. Behzad
From: Behzad Sadeghi on 25 Jul 2010 15:48 On Jul 25, 11:44 pm, Jeroen Mostert <jmost...(a)xs4all.nl> wrote: > On 2010-07-25 19:57, Behzad Sadeghi wrote:> Reading your answer, I realized I have another problem. Let's say I > > run the stored procedure from within a T-SQL script, either using > > FMTONLY or not. I will get a result set, either empty or not. Now, how > > can I examine this result set to decipher its columnar structure from > > within my T-SQL script? I know I can use "insert<myTable> exec > > <myProc>", which will persist a table in my database, which I can then > > examine using system catalog views. But for that, I already need to > > know the structure of the result set. Is there a way to do this > > (persist the result set to the database) without knowing its > > structure. Again, I need to do all this from within T-SQL. > > It's possible, but I wouldn't bother. SELECT * INTO ... FROM OPENQUERY(...., > 'SET FMTONLY ON; EXEC ...') should give you a table to analyze. You'll need > to add the local server as a linked server enabled for data access. > OPENROWSET should also work, but this requires the "ad hoc distributed > queries" server option to be changed. > > It is much more sane to do schema determination from client code (for > example, using CommandBehavior.SchemaOnly in .NET, but other libraries will > probably have similar options). Dynamic SQL is tricky enough without adding > yet another layer of indirection, and T-SQL is a poor candidate for a > general programming language. Robustness and maintainability are especially > hard to achieve, which is rather undesirable for a testing harness. > > I doubt you really "need to do all this from within T-SQL": with a little > more ingenuity and/or script-fu this shouldn't be necessary. Look into stuff > like PowerShell and IronPython if you don't want to write complete > applications. A complete, small C(++) application to do the tricky schema > determination bits would also make sense. > > Something has to trigger executing that T-SQL. Unless it's a scheduled job, > it should also give you an opportunity for executing client code. > > -- > J. Thanks Jeroen. Lots to think about in your reply. Thanks again.
From: Erland Sommarskog on 25 Jul 2010 17:21
Behzad Sadeghi (behzad.sadeghi(a)gmail.com) writes: > Reading your answer, I realized I have another problem. Let's say I > run the stored procedure from within a T-SQL script, either using > FMTONLY or not. I will get a result set, either empty or not. Now, how > can I examine this result set to decipher its columnar structure from > within my T-SQL script? I know I can use "insert <myTable> exec ><myProc>", which will persist a table in my database, which I can then > examine using system catalog views. But for that, I already need to > know the structure of the result set. Is there a way to do this > (persist the result set to the database) without knowing its > structure. Again, I need to do all this from within T-SQL. I can only agree with Jeroen, don't try do this from T-SQL, unless you really love to hurt yourself. You can do it all in SQL Server if you insist, but then you should write your discovery routines in C# ort VB .Net. But it's probably better to have it it all in a client-side program. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |