Is there any way of accessing the resultset of an EXEC comand?

Hello guys,
I have a trouble in here to do something that would be very simple to solve
in an Oracle database thru the DBMS_SQL package.
I have to execute a query inside a stored procedure, but i don't know what
is the column name to bring in the resultset before running the procedure, i
have to do some work inside the stored procedure to get this column name. So
,
at this part of my program, i have to build a query string dinamically as it
follows:
SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
EXEC(@.SQL)
The problem is that i can't access the resultset of a SELECT executed with
the EXEC command.
I've already tried to use the OPENROWSET method, which seems to be too much
for my needs, but in this case the problem is that the query argument of thi
s
method should be a constant. I've tried also the API cursors library
(sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the values
of the resultsets to any variables in my T-SQL program, so i felt in the sam
e
case as the queries executed with EXEC, i have no way to access its
resultsets.
Does anybody know a way of solving this problem? (Sorry for my english, i'm
brazilian doing my best to express myself).
Thanks,
Paulo RicardoYes, you can use a Temp Table.. here's an example using Northwid Database...
Use Northwind
Create Table #NewTab
(CID VarChar(20),
CustNm VarChar(80),
ContNm VarChar(80))
Declare @.S VarChar(300)
Set @.S = 'Select CustomerID, CompanyName, ContactName From Customers'
-- ---
Insert #NewTab(CID, CustNm, ContNm) Exec (@.S)
-- ---
Select * From #NewTab
-- --
hth, Charly
Charles Bretana Jr.
Arete Industries Inc.
"Paulo Ricardo" wrote:

> Hello guys,
> I have a trouble in here to do something that would be very simple to solv
e
> in an Oracle database thru the DBMS_SQL package.
> I have to execute a query inside a stored procedure, but i don't know what
> is the column name to bring in the resultset before running the procedure,
i
> have to do some work inside the stored procedure to get this column name.
So,
> at this part of my program, i have to build a query string dinamically as
it
> follows:
> SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
> EXEC(@.SQL)
> The problem is that i can't access the resultset of a SELECT executed with
> the EXEC command.
> I've already tried to use the OPENROWSET method, which seems to be too muc
h
> for my needs, but in this case the problem is that the query argument of t
his
> method should be a constant. I've tried also the API cursors library
> (sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the valu
es
> of the resultsets to any variables in my T-SQL program, so i felt in the s
ame
> case as the queries executed with EXEC, i have no way to access its
> resultsets.
> Does anybody know a way of solving this problem? (Sorry for my english, i'
m
> brazilian doing my best to express myself).
> Thanks,
> Paulo Ricardo|||Paulo
Also you can use sp_executesql stored procedure.
declare @.sql nvarchar(1000),
@.col sysname,
@.orderid int
select @.col='OrderID', @.orderid=10248
set @.sql = 'select * from Northwind..Orders where '+@.col+'= @.ordid'
exec sp_executesql @.sql, N'@.ordid int',@.orderid
"Paulo Ricardo" <Paulo Ricardo@.discussions.microsoft.com> wrote in message
news:B55806A1-E2BD-40F0-B62D-EE54E201EA0E@.microsoft.com...
> Hello guys,
> I have a trouble in here to do something that would be very simple to
solve
> in an Oracle database thru the DBMS_SQL package.
> I have to execute a query inside a stored procedure, but i don't know what
> is the column name to bring in the resultset before running the procedure,
i
> have to do some work inside the stored procedure to get this column name.
So,
> at this part of my program, i have to build a query string dinamically as
it
> follows:
> SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
> EXEC(@.SQL)
> The problem is that i can't access the resultset of a SELECT executed with
> the EXEC command.
> I've already tried to use the OPENROWSET method, which seems to be too
much
> for my needs, but in this case the problem is that the query argument of
this
> method should be a constant. I've tried also the API cursors library
> (sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the
values
> of the resultsets to any variables in my T-SQL program, so i felt in the
same
> case as the queries executed with EXEC, i have no way to access its
> resultsets.
> Does anybody know a way of solving this problem? (Sorry for my english,
i'm
> brazilian doing my best to express myself).
> Thanks,
> Paulo Ricardo