I was speaking to my friend Byer today, discussing the use of the OpenQuery function to get to some data from Oracle (in a linked server) - a requirement of the system on which he was working. More specifically, he needed to lookup a single value based in Oracle and return it to SQL Server to use in a query. Since OpenQuery does not allow a variable to be used for the SQL statement, and since this particular query needed a variable, dynamic execution was going to have to be used to create the correct SELECT statement sing the OpenQuery function. So, after sorting out these little details, we were finally at a point where we could use the value coming back from the dynamically executed SQL statement.

So, I will keep the OpenQuery stuff out of this to show what happened. Neither of us had never tried the following little morsel of code; and we discovered there is something wrong with it.

DECLARE @OneValue Table (Val Varchar(11))
DECLARE @sExec Varchar (1000)

SET @sExec = 'SELECT au_id FROM authors where au_lname = ''MacFeather'''

INSERT INTO @OneValue
EXECUTE (@sExec)

SELECT * FROM @OneValue

Now that I think about it, I'm going to give the answer in my next post. Enjoy....