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

  • 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

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

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

  • 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

  • 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

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

  • 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.

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

  • 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.

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

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