sql - How do I use a stored procedure in a function or query? -

sorry title. not think of way sum problem in short sentence.

in select statement, table has column of active directory id's. need use subquery full names adid's. came following code query adsi , need:

select givenname + ' ' + sn fullname openrowset( 'adsdsoobject', 'adsdatasource'; 'mydomain\jsmith'; 'mypassword',  'select givenname, sn ''ldap://my.company.com'' objectclass = ''user'' , samaccountname = ''stennell''') 

that's cool, need parameterize id, password, , samaccountname value. since openrecordset requires literal strings, can't concatenate strings & parameters. must use dynamic sql. here's new code:

set @sql = 'select givenname + '' '' + sn fullname ' + 'from openrowset(''adsdsoobject'', ''adsdatasource''; ''us\' + @authadid + '''; ''' + @authpwd + ''',' + '''select givenname, sn ''''ldap://us.ups.com'''' objectclass = ''''user'''' , samaccountname = ''''' + @searchadid + ''''''')'  exec sp_executesql @sql 

that works, wrap stored procedure. can't use exec in query. google searching told me needed put code in scalar-valued function. tried that, got error when trying create function:

only functions , extended stored procedures can executed within function.

so more google searching told me create sql clr function in visual studio. don't have template tutorials told me choose (sql clr c# user defined function). have sql tools vs installed, still no templates.

i need result need:

left outer join (select fullname (exec usp_queryadsi 'jsmith','mypassword', tiv.userid)) ad on tiv.userid = ad.adid 

obviously, doesn't work because can't use exec that. i've hit brick wall. hoping can shed light.

a question you. mean "can't use exec in query"? can use various forms of exec in stored procedures. security on our server such can't test you're trying do, 1 thing jumps out @ me that

exec sp_executesql @sql 

could be

exec (@sql) 

i know works sorts of queries, haven't tried openrowset