Automated way to discover the structure of the result set ofa stored procedure?
in [Server Configuration]
|
Prev: Automated way to discover the structure of the result set of a stored procedure?
Next: GHD IV MINI Styler
From: Jeroen Mostert on 25 Jul 2010 15:44 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. |