Search This Blog

Pass a variable to a linked server query

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)

No comments: