Hi All,

I have a problem about assigning the results of an OPENQUERY. Please check the code below:

DECLARE @sqlString nvarchar(4000)
DECLARE @sqlString1 nvarchar(4000)
DECLARE @custName nvarchar(100)

SET @custID = 2
SET @sqlString1 = 'SELECT * FROM myTable WHERE CustID = ' + CAST( @custID as varchar(6))
SELECT @sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
EXECUTE(@sqlString)

--
The above code works fine but i need something like
SELECT @sqlString = 'SELECT @custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
EXECUTE(@sqlString)

By doing this i want to assign the CustName to the local variable @custName.
I tried to use a temp table but in that case i was not able to specify a where clause in @sqlString1. (i'd rather use linked_server.databasename.owner.tablename , but i read that this does not provide good performance )

Any suggestions about the problem are welcomed!
Thanks in advance,

Bahtiyar KARANLIK

declare @sqlString nvarchar(1000)
SELECT @sqlString = 'SELECT @custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
EXEC sp_executesql @sqlString, N'@custname varchar(20) out', @custname out

or

SELECT @sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @sqlString1 + ''')'
create table #a (s varchar(20))
insert #a
exec (@sqlstring)

select @custname = s from #a
drop table #a

Using the 4 part name in the query should be just s good. If you are joining with strings then make sure the servers are collation compatible so that the filter is performed on the remote server and check the query plan.
This sort of thing can give bad performance if used as a join to a local table but if you are just selecting using constants for a filter it should be OK.

DECLARE @custName nvarchar(100)
SELECT @custName = CustName FROM OPENQUERY(DEEPACCESS,'SELECT CustName FROM myTable WHERE CustID = 2')
SELECT @custName as CustName

Try it.