How to Embed Variable Value in SQL Server query

Question

I need to be able to query Active Directory from SQL Server - see https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

The following runs fine:

DECLARE @eid nvarchar(5)
DECLARE @username nvarchar(5)

SET @eid='123'

SELECT @username=sAMAccountName FROM OpenQuery(ADSI,'SELECT sAMAccountName, employeeID FROM ''LDAP://mydomain.com/DC=mydomain,DC=com'' WHERE objectClass = ''User'' AND employeeID=123')
PRINT  @username

How to replace the hardcoded 123 value with the actual value of the @eid, so that the modified code will run fine as well?

Thanks!

score 0 · Answer 1 · edited May 23 '17 at 12:15

Annoyingly you have to build the SQL string ahead of time to add a parameter value into it and then execute the OpenQuery using that string. You must execute all of it as you would a dynamic query EXEC sp_executeSQL ...

Here's what I came up with:

DECLARE @eid nvarchar(5), @username nvarchar(5), @Query nvarchar(MAX)

SELECT @eid='123'
SELECT @Query = N'SELECT sAMAccountName FROM OpenQuery(ADSI, ''SELECT sAMAccountName, employeeID FROM ''''LDAP://mydomain.com/DC=mydomain,DC=com'''' WHERE objectClass = ''''User'''' AND employeeID=' + @eid + ''')'
SELECT @Query

EXEC sp_executeSQL @Query, @username OUTPUT
PRINT  @username

I may not have the output quite right. Another option you have is to insert the output of the EXEC sp_executeSQL into a temptable and select the username from that value. For more info you can see this question.

Another option (and probably the easiest of all) is to move the WHERE outside of the open query and filter after you get the results back like this:

DECLARE @eid nvarchar(5), @username nvarchar(5), @Query nvarchar(MAX)

SELECT @eid='123'

SELECT @username = sAMAccountName--, employeeID
FROM OpenQuery(AD, 'SELECT sAMAccountName, employeeID FROM ''LDAP://mydomain.com/DC=mydomain,DC=com'' WHERE objectClass = ''User''')
WHERE employeeID = @eid

PRINT @username

score 0 · Answer 2 · answered Jan 06 '21 at 14:01

I know this is an old question, but here is a way to work with output values (since you'll return only 1 value):

DECLARE @eid nvarchar(5), @username nvarchar(50), @Query nvarchar(MAX)
SET @Query = '(SELECT @username = sAMAccountName FROM OpenQuery(ADSI,''SELECT sAMAccountName, employeeID FROM ''LDAP://mydomain.com/DC=mydomain,DC=com'' WHERE objectClass = ''User'' AND employeeID=' + @eid +') AS tblADSI)'
EXEC sp_executesql @Query , N'@username nvarchar(50) out', @username out
SELECT @username As Outputs

This will assign the result of the OpenQuery execution, in the variable @username.

We tested for Store procedure in MSSQL 2012, but should work with MSSQL 2008+.

Microsoft Says that sp_executesql(Transact-SQL): Applies to: SQL Server (SQL Server 2008 through current version), Windows Azure SQL Database (Initial release through current release). (http://msdn.microsoft.com/en-us/library/ms188001.aspx)

How to Embed Variable Value in SQL Server query

2 Answers2