How do I, in effect, SELECT FROM a stored procedure

Question

Post reply

How do I, in effect, SELECT FROM a stored procedure

Christian Bahnsen

SSC Eights!

Points: 969
More actions
November 26, 2010 at 2:51 pm

#231604

How can I use the output from a system stored procedure as the input (FROM) for a select statement?
I would like to be able to pass a table name to sp_columns_90 and display just the COLUMN_NAME, TYPE_NAME, and PRECISION columms, something like:
DECLARE @myTable AS NVARCHAR(50)
SET @myTable = 'foo'
SELECT COLUMN_NAME, TYPE_NAME, PRECISION
FROM (EXEC sp_columns_90 @table_name = @myTable)
I was able to create a report in SSRS that effectively does this using sp_columns_90 and a parameter that has a drop-down list driven by a second dataset listing all the tables. Is it possible with straight T-SQL?
It's akin to pipelining in Powershell but in T-SQL do I have to use a temp table? I tried using a CTE or UDF, but neither approach worked for me.
Thanks in advance for any help.
Chris

Viewing 9 posts - 1 through 8 (of 8 total)

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

dataguru1971 SSC Veteran Points: 267 More actions · Answer 1

Why not populate both lists from INFORMATION_SCHEMA.tables and .columns views which exist?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Create a table and then use INSERT/EXEC or just use OPENROWSET.

--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)

Christian Bahnsen SSC Eights! Points: 969 More actions · Answer 3

dataguru1971,

Thanks for the suggestion. This worked like a charm:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'foo'

I wasn't aware of the INFORMATION_SCHEMA. I can see other uses for it.

Thanks again.

Chris

Christian Bahnsen SSC Eights! Points: 969 More actions · Answer 4

Jeff,

Thanks for the suggestion. Among others, I'd looked over Erland Sommarskog's article on How to Share Data Between Stored Procedures

(http://www.sommarskog.se/share_data.html). I was trying to avoid the create-a-table approach, hoping instead to "pipeline" the sproc resultset.

The instance of SQL 2008 I'm working off seems to have OPENROWSET disabled. I need to do some research on it before asking our accidental DBA to enable it.

The INFORMATION_SCHEMA approach looks simpler and works like a charm.

Thanks again for your suggestion.

Chris

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Actually, my bad. I didn't read the whole post to see what the data source actually was. I agree that the Information_Schema views are the ticket in this case.

--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)

dataguru1971 SSC Veteran Points: 267 More actions · Answer 6

Jeff Moden (11/29/2010)
Actually, my bad. I didn't read the whole post to see what the data source actually was. I agree that the Information_Schema views are the ticket in this case.

😀 I kept re-reading that thinking i must have missed something for the Modenator to respond with something different. I am a big fan by the way, and people at my former job are still email me questions about how I made use of the Quirky Update[/url].

Christian Bahnsen (11/29/2010)
dataguru1971,
Thanks for the suggestion. This worked like a charm:
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'foo'
I wasn't aware of the INFORMATION_SCHEMA. I can see other uses for it.
Thanks again.
Chris

Indeed there are many other potential uses.

I have made frequent use of the IS views..adding specific extended properties and making crude, but "live" View based "data dictionaries" is one of them. Instead of users having to point to Red-Gate produced documents or other documents, they can just query a view and see the descriptions attached to the column/table etc. Not a catch all solution or perfect, but the views make some tasks fairly simple.

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

dataguru1971 (11/29/2010)
I kept re-reading that thinking i must have missed something for the Modenator to respond with something different. I am a big fan by the way, and people at my former job are still email me questions about how I made use of the Quirky Update.

Thanks for the wonderful compliment. I'm tickled that you were able to successfully deploy the technique. I hope that you're one of those that actually follows all the rules for the Quirky Update. 😉

Speaking of Quirky Update, Paul White came up with a pretty cool way of bullet-proofing the Quirky Update in the discussion that followed that article. It's nearly identical to a method that Peter Larsson came up with for doing "ordered updates" but it has a built in check to ensure the order is never violated without an error being thrown. Oddly enough, the method forces it to work correctly at (from what I can see so far) all times. Tom Thompson modified that a bit to make it even more bullet proof. Obviously, there will be one (hopefully, final) more rewrite once I get done testing a similar method for folks to use with SQL Server 2000 on top of the new method for 2k5 and up.

--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)

dataguru1971 SSC Veteran Points: 267 More actions · Answer 8

Jeff Moden (11/29/2010)
dataguru1971 (11/29/2010)
I kept re-reading that thinking i must have missed something for the Modenator to respond with something different. I am a big fan by the way, and people at my former job are still email me questions about how I made use of the Quirky Update.
Thanks for the wonderful compliment. I'm tickled that you were able to successfully deploy the technique. I hope that you're one of those that actually follows all the rules for the Quirky Update. 😉
Speaking of Quirky Update, Paul White came up with a pretty cool way of bullet-proofing the Quirky Update in the discussion that followed that article. It's nearly identical to a method that Peter Larsson came up with for doing "ordered updates" but it has a built in check to ensure the order is never violated without an error being thrown. Oddly enough, the method forces it to work correctly at (from what I can see so far) all times. Tom Thompson modified that a bit to make it even more bullet proof. Obviously, there will be one (hopefully, final) more rewrite once I get done testing a similar method for folks to use with SQL Server 2000 on top of the new method for 2k5 and up.

I got it work no problem..on SQL 2000 no less, but it easily worked on 2005 as well when we upgraded. I first deployed it back in late 2007 after seeing the article and getting curious if it would suit my need. Thanks Jeff..anyways, no need to hijack this thread more..I will look forward to the newest version.