SQL Server OpenQuery - Passing parameter result from main SELECT query to sub query

Question

I am creating a report in SSRS that returns a bunch of data, one of the columns I need to return is a value from Active Directory. I have a query which gets a value that I want to pass into a subquery which looks a value up in Active Directory.

What I have so far looks something like this (simplified):

DECLARE @UserID int
SELECT @UserID = UserID FROM dbo.Users As TEST WHERE (RecordID = 123456) 

(SELECT displayName FROM OpenQuery (
 ADSI,  
  'SELECT displayName
  FROM  ''LDAP://DC=company,DC=local'' 
  WHERE objectCategory=''user'' AND extensionattribute5='' +@UserID+ ''
  ') ) AS LoggedBy

FROM dbo.UserRecords WHERE UserID = 1

The problem is however when I use @UserID in the subquery the output alias "LoggedBy" always returns "Null". If I take the parameter out and manually type the value that the parameter has, it works fine.

It seems like I cannot get the @UserID paramter from the main query into the sub-query. ust wondering, what am I missing here?

Answer 1

You can't send variables into OpenQuery context in this way. Try to filter the query "outside":

DECLARE @UserID int
SELECT @UserID = UserID FROM dbo.Users As TEST WHERE (RecordID = 123456) 

(SELECT t.displayName FROM OpenQuery (
 ADSI,  
  'SELECT displayName, extensionattribute5
  FROM  ''LDAP://DC=company,DC=local'' 
  WHERE objectCategory=''user''
  ') as t
 WHERE t.extensionattribute5=@UserID ) AS LoggedBy


FROM dbo.UserRecords WHERE UserID = 1

From MSDN

OPENQUERY does not accept variables for its arguments.