richkeegan
asked on
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
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
ASKER
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
I'll see if anyone else has any better ideas. If not then you can get the vote of confidence.
Thanks,
Rich
simonsabin is right - it is not possible
Three votes for NOT POSSIBLE.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now you're talking...
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>, ... )
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>, ... )
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..#TempTb
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
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..#TempTb
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
Youcan put the result of a sp into a table by
INSERT INTO mytable EXEC sp_users