When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query.
To pass a variable to one of the pass-through functions, you must build a dynamic query.
DECLARE @USER AS VARCHAR(10)
SET @USER='CROMER'
declare @query VARCHAR(1000)
SET @query = '
SELECT SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU
FROM OPENQUERY( ADSI, ''SELECT * FROM ''''LDAP:// DC=sd_corp,DC=local'''' WHERE sAMAccountName = ''''' + @USER + ''''''')'
EXEC(@qUERY)
