How to get results from an SP into a table.

Question

How to get results from an SP into a table.

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Perhaps you can explain what type of environment you are working in where you are going to be running arbitrary stored procedures (which may or may not take parameters) and return the first result set from that procedure (some may return multiple results sets, MARS) )back to the user?

I have never worked in such an environment and am curious about this.

When I invoke a stored procedure I know if it takes parameters and if so what those parameters are, I know what is being returned by the stored procedure (if it is returning a result set or multiple result sets (sorry, but as far as I know T-SQL itself still doesn't handle MARS yet).

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

ben.brugman (6/12/2013)

I still think your solution needs to know the output format before you make the call.

Yes, I was making the assumption that the procedure's output was known. I'm not sure I'd want to run any procedure without knowing what output to expect.

Look at this script I wrote and perhaps you can pull some useful tricks out of it to get what you need. The script was designed to output text only to the SSMS messages window, but it would be easy to stuff the results into a table.

GENERATE COLUMN METADATA[/url]

And this snippet will give you the parameters for a procedure:

DECLARE

@SchemaName sysname

,@ProcedureName sysname

SET @SchemaName = 'dbo'

SET @ProcedureName = 'myTestProc'

SELECT

iscol.ORDINAL_POSITION AS ID

,iscol.PARAMETER_NAME

,iscol.DATA_TYPE

,(CASE

WHEN (iscol.DATA_TYPE IN ('char','varchar','nchar','nvarchar')

AND iscol.CHARACTER_OCTET_LENGTH = -1)

THEN '(MAX)'

WHEN iscol.DATA_TYPE IN ('char','varchar')

THEN '('+CAST(iscol.CHARACTER_OCTET_LENGTH AS VARCHAR)+')'

WHEN iscol.DATA_TYPE IN ('nchar','nvarchar')

THEN '('+CAST(iscol.CHARACTER_OCTET_LENGTH/2 AS VARCHAR)+')'

WHEN iscol.DATA_TYPE = 'decimal'

THEN '('+CAST(iscol.NUMERIC_PRECISION AS VARCHAR)+','+CAST(iscol.NUMERIC_SCALE AS VARCHAR)+')'

ELSE ''

END) AS DATA_LENGTH

FROM

INFORMATION_SCHEMA.PARAMETERS AS iscol

WHERE

iscol.SPECIFIC_SCHEMA = @SchemaName

AND iscol.SPECIFIC_NAME = @ProcedureName

ORDER BY

iscol.ORDINAL_POSITION

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Lynn Pettis (6/12/2013)
Perhaps you can explain what type of environment you are working in where you are going to be running arbitrary stored procedures (which may or may not take parameters) and return the first result set from that procedure (some may return multiple results sets, MARS) )back to the user?
I have never worked in such an environment and am curious about this.
When I invoke a stored procedure I know if it takes parameters and if so what those parameters are, I know what is being returned by the stored procedure (if it is returning a result set or multiple result sets (sorry, but as far as I know T-SQL itself still doesn't handle MARS yet).

My company works with a (very) large number of databases. For myself I work with a number of databases, my main responsibility is around SQL-server. Within the SQL-server domain I work with a number of databases. These databases have different designs and different uses.

For a number of databases I have been involved in designing/developing the database, so for those databases I am fairly familiar what the design and the implementation is for those databases.

For other databases I have not been involved with designing and developing of the database, also I do not know what design decisions were taken for those databases and what implementation decisions where taken for those databases.

So there is a number of area's where I could use the technique I asked for.

Because I am the database consultant for both a number of the databases and for SQL-server, I often get the question: What is returned by a stored procedure? Or more difficult which stored procedure can return these results. Offcourse I do agree with you that this should be documented. But even the best (and we do not have the best) documentation is often different from the actual implementation. You actually have to look at the implementation of a database to see what a stored procedure is exactly doing. With the number of stored procedures and the complexity of a large number of these procedures it does help to know what the result set is and what all the types of it are.

If I get my 'hands' on only the first resultset this would allready be a great help. The MARS problem is smal compared to the variations in resultsets from procedures. There are not many stored procedures delivering more than one resultset. And there are loads of procedures which are parameterized on how the output is. (Which columns, which order of columns etc.).

There are some stored procedures which are only used in a few ways (sometimes 1 sometimes 3), but can produce such a great number of different resultsets that this comes close to the number of particles in the complete universe. So there is no way to investigate all the resultsets posibilities for these, but as said only a few variations are used.

If one would build a database of the produced resultsets of the stored procedures, one would be able to search which stored procedure is producing which fields/types of results etc.

So you can search the existing stored procedures for something you need.

Thanks for your interrest in my problem,

Ben Brugman

ben.brugman SSChampion Points: 13369 More actions · Answer 4

Steven Willis (6/12/2013)
I'm not sure I'd want to run any procedure without knowing what output to expect.

First of all I do run the stored procedure in an environment where running anything can not do any harm. This can be a testing environment or a development environment.

Secondly Using naming conventions you can see if a stored procedure does modify or retrieval only.

In general I do not run the modifying stored procedures even on a testing/developing system.

Having a formalised resultset makes it far easier to understand a retrieving stored procedure.

The input parameter can easely be obtained form the information_schema, no problem there. (Wel you still have to know or guess the effect of each input parameters).

Thnx

ben

g_demetriou Ten Centuries Points: 1168 More actions · Answer 5

This might be of help to you if you are searching within a database for an sp or view that contains a certain table name, field name, or text:

SELECT DISTINCT ob.name

FROM syscomments cm

INNER JOIN sysobjects ob ON cm.id=ob.id

WHERE cm.TEXT LIKE '%text%'

ben.brugman SSChampion Points: 13369 More actions · Answer 6

g_demetriou (6/14/2013)
This might be of help to you if you are searching within a database for an sp or view that contains a certain table name, field name, or text:
SELECT DISTINCT ob.name
FROM syscomments cm
INNER JOIN sysobjects ob ON cm.id=ob.id
WHERE cm.TEXT LIKE '%text%'

Thank you,

This indeed shows the KNOWN dependencies, and also knowing the dependencies is very valuable. But this does not help for the resultset, and a lot of the stored procedures use objects which are not registered in the dependency tables. (Dynamic code and other constructs prevent that).

Thx

Stef

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 7

ben.brugman (6/14/2013)
g_demetriou (6/14/2013)
This might be of help to you if you are searching within a database for an sp or view that contains a certain table name, field name, or text:
SELECT DISTINCT ob.name
FROM syscomments cm
INNER JOIN sysobjects ob ON cm.id=ob.id
WHERE cm.TEXT LIKE '%text%'
Thank you,
This indeed shows the KNOWN dependencies, and also knowing the dependencies is very valuable. But this does not help for the resultset, and a lot of the stored procedures use objects which are not registered in the dependency tables. (Dynamic code and other constructs prevent that).
Thx
Stef

I agree with what you said about "dependencies" but the code quoted above doesn't make use of any of the dependency tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ben.brugman SSChampion Points: 13369 More actions · Answer 8

Jeff Moden (6/15/2013)
I agree with what you said about "dependencies" but the code quoted above doesn't make use of any of the dependency tables.

Sorry, should have read more carefully, my bad. But the problem remains the same.

thanks,

ben