OUTPUT variable from dynamic query with openquery

_{Monday, March 16, 2015 9:38 PM}

I am working on a dynamic sql statement that uses openquery to retrieve some columns from an Oracle source.  My goal is to take the variables from the select openquery statement and store them, so they can be passed to an insert statement.  I am having no problem getting the emplid but the first_name and last_name are NULL

Any help would be appreciated.

DECLARE @sql   nvarchar(max)
declare @emplid varchar(11)
declare @first_name varchar(50)


set @emplid = '1234'

BEGIN
    SET NOCOUNT ON;



SET @sql = 'select distinct emplid, 
                First_name  , 
                Last_name 
                from openquery(DWHCRPT, ''select p.emplid,  p.First_nAME, p.last_nAME
                                FROM PS_OCC_BIODEMO_S P 
                                where P.emplid = ''''' + @emplid + ''''''') A';  
    EXEC SP_executesql @SQL, N'@emplid VARCHAR (11), @first_name varchar(50) OUTPUT', @emplid, @first_name = first_name;
select @emplid, @first_name --currently returning NULL
END

_{Monday, March 16, 2015 11:10 PM ✅Answered}

Patrick's query would work, but it would drag the entire table over to SQL Server, which could be expensive.

The code you posted have several flaws. You are not assigning @first_name, and you have failed to provide OUTPUT for the actual parameter. Also, the DISTINCT looks out of place. Isn't emplid a key?

This should work better:

SET @sql = 'select @first_name = First_name 
        from openquery(DWHCRPT, ''select p.First_nAME, p.last_nAME
                                      FROM PS_OCC_BIODEMO_S P
                   where P.emplid = ''''' + @emplid + ''''''') A'; 
   EXEC SP_executesql @SQL, N'@emplid VARCHAR (11), @first_name varchar(50) OUTPUT', @emplid, @first_name = @first_name OUTPUT;

select @emplid, @first_name --currently returning NULL

Also, look at this link for some tips how to write queries with OPENQUERY without going insane over all nested quotes:
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Monday, March 16, 2015 9:48 PM}

Could you not:

DECLARE @sql nvarchar(max), @emplid varchar(11), @first_name varchar(50), @last_name varchar(50)
SET @emplid = '1234'

SELECT DISTINCT @first_name = first_name, @last_name = Last_name 
  FROM OPENQUERY(DWHCRPT, 'SELECT p.emplid,  p.First_nAME, p.last_nAME
  FROM PS_OCC_BIODEMO_S P')
 WHERE emplid = @emplid

SELECT @first_name, @last_name

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.