Select * from a stored procedure ????

Hi,
I want to do a select statement based on a stored procedure. I am unable to find the correct syntax at this time. Can anyone please help. I have a stored procedure called say sp_users. Is it possible to do something like:

select username from sp_users

Many thanks,
Rich
richkeeganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simonsabinCommented:
It is not possible.

Youcan put the result of a sp into a table by
INSERT INTO mytable EXEC sp_users

0
richkeeganAuthor Commented:
I'm not so sure that it's not possible. I don't really want to be messing around with temp tables.
I'll see if anyone else has any better ideas. If not then you can get the vote of confidence.
Thanks,
Rich
0
tchalkovCommented:
simonsabin is right - it is not possible
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

formulaCommented:
Three votes for NOT POSSIBLE.
0
simonsabinCommented:
Might go for president at this rate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
richkeeganAuthor Commented:
Now you're talking...
0
HenryBStinsonCommented:
One problem is that a stored procedure PASSES  BACK a recordset as an output parameter, so that if we want to include the output cursor of an SP in a SQL query (that could be in another SP), we need to do something like this (using SQL Server syntax):

DECLARE @cursor_1          CURSOR

EXEC usp_StoredProc <parameter 1 value>, <parameter 2 value>, ... , @cursor_1

Select * from @cursor_1

(assuming that the output cursor is the last parameter, which would also allow ADO to easily retrieve the recordset)

Now, this doesn't work the way it is, it's just that the previous answers didn't address this aspect.  I'm actually trying to figure out now how to use the recordset (cursor) from one SP inside the SQL query that is inside another SP (in SQL Server).  So far, this has eluded me, but I know I saw something on a website about how to do this.  But you can't just do:

   Select * from  (EXEC usp_StoredProc <parameter 1 value>, <parameter 2 value>, ... )
0
HenryBStinsonCommented:
This works (SQL Server syntax) by inserting into a temp table.  Not sure how to do this in Oracle.
The SP it calls has to not pass the recordset back as a parameter, but just have a SELECT statement at the end.  The EXEC seems to kind of pass back the recordset from the SP that is EXEC'ed.


    If Object_Id('tempdb..#TempTbl_1') IS Null
        CREATE TABLE #TempTbl_1 (
            proj_id                INTEGER,
            Level_No               INTEGER,
            wbs_id                 INTEGER           NOT NULL,
            wbs_path               VARCHAR(100),
            proj_node_flag         VARCHAR(1),
            wbs_parent_id          INTEGER,
            wbs_short_name         VARCHAR(32)   NOT NULL,
            wbs_name               VARCHAR(100)  NOT NULL,
            activity_id            VARCHAR(24),
            activity_name          VARCHAR(120)
        )
    Else
        TRUNCATE TABLE #TempTbl_1

    INSERT INTO #TempTbl_1
        EXEC usp_Get_WBS @p_Project_ID

        OPEN @p_ref_cursor_out FOR
         
               SELECT DISTINCT
                               wbs.Level_No,
                               wbs.wbs_id,
                               wbs.wbs_1.WBS_Path ,
                               t1.task_code,
                               t1.task_name
               FROM ( SELECT Level_No,
                             wbs_id,
                             WBS_Path
                         FROM #TempTbl_1
                        ) wbs
              INNER JOIN Task t
                 ON t.Proj_id = wbs.proj_id
                 AND t.wbs_id = wbs.wbs_id
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.