How To Filter An EXEC Table

Hi All,

How do I filter an EXEC table as to put the returned value into a variable?

I have to filter an EXEC table because I am using a table variable to define which table tto select.

Help appreciated.

Hi,

I suppose you're talking about a stored procedure (or a self generated sql script you execute with the EXEC statement).

I'm afraid you can't do this by this way in sql server (you could with a development tool).

The solution could be using a Function, which returns a table.

|||

Donaghy:

If you can post your code that doesn't work correctly we can perhaps get a conceptual idea of what you want to do and can help you work around the issue. Also, even though you cannot use a table variable to receive output from an exeucte, you can use a temp table.

Dave

|||

This is the statement I'm using:

declare @.Conn varchar(1000)
set @.Conn = 'This is the table connection'

DECLARE @.strSQL varchar(500)
SET @.strSQL = 'SELECT Name FROM ' + @.Conn + ' WHERE Name = ''System'''
EXEC(@.strSQL)

the returned value is a single value so need to take that value and put it into a Temp table

Any suggestions.

|||

Very good; try this:

declare @.Conn varchar(1000)
set @.Conn = 'This is the table connection'

DECLARE @.strSQL varchar(500)
SET @.strSQL = 'SELECT Name FROM ' + @.Conn + ' WHERE Name = ''System'''

create table #results (results varchar (255) null) -- provided 255 is large enough!

insert into #results
EXEC(@.strSQL)

|||thanks .. will give that a try |||

No luck ... got this error:

The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Any suggestions ?